Replace in Power BI

Question

I have the below two tables imported to power bi from SQL database:

<table border="1">
<tr><th>Id</th><th>Name</th></tr>
<tr><td>1</td><td>One</td></tr>
<tr><td>2</td><td>Two</td></tr>
<tr><td>3</td><td>Three</td></tr>
<tr><td>4</td><td>Four</td></tr>
<tr><td>5</td><td>Five</td></tr>
</table>
<br>
<br>
<table border="1">
<tr><th>Selected Ids</th></tr>
<tr><td>1,2,3</td></tr>
<tr><td>1,3</td></tr>
<tr><td>1</td></tr>
<tr><td>3</td></tr>
<tr><td>1</td></tr>
<tr><td>BLANK</td></tr>
<tr><td>BLANK</td></tr>
</table>

Now, i need to add a calculated column to the second table where Ids are replace with the Name. Like,

<table border="1">
<tr><th>Selected Ids</th><th>Names</th></tr>
<tr><td>1,2,3</td><td>One,Two,Three</td></tr>
<tr><td>1,3</td><td>One,Three</td></tr>
<tr><td>1</td><td>One</td></tr>
<tr><td>3</td><td>Three</td></tr>
<tr><td>1</td><td>One</td></tr>
<tr><td>BLANK</td><td>BLANK</td></tr>
<tr><td>BLANK</td><td>BLANK</td></tr>
</table>

How can i achieve this in an efficient manner and avoid creating multiple columns.

Thanks in Advance.

score 1 · Answer 1 · answered Aug 04 '17 at 16:46

Well, There might be a better way, but I used Power Query to get this:

I started with this as Table1:

and this as Table2:

Then I worked within Table2's query.

Here's the M code:

let
Source = Excel.Workbook(File.Contents("C:\Users\mpincince\Desktop\SelectedIds.xlsx"), null, true),
Table2_Table = Source{[Item="Table2",Kind="Table"]}[Data],
#"Changed Type" = Table.TransformColumnTypes(Table2_Table,{{"SelectedIds", type text}}),

//The lines above established Table2. The following lines address your question...

#"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1),
#"Added Table1Copy" = Table.AddColumn(#"Added Index", "Table1Copy", each Table1),
#"Expanded Table1Copy" = Table.ExpandTableColumn(#"Added Table1Copy", "Table1Copy", {"Id", "Name"}, {"Id", "Name"}),
#"Added IdComparison" = Table.AddColumn(#"Expanded Table1Copy", "NameOfMatchedId", each if [SelectedIds]<> "" then (if Text.Contains([SelectedIds],[Id]) then [Name] else false) else null),
#"Filtered Rows" = Table.SelectRows(#"Added IdComparison", each ([NameOfMatchedId] <> false)),
#"Grouped Rows" = Table.Group(#"Filtered Rows", {"Index", "SelectedIds"}, {{"Names", each ([NameOfMatchedId])}}),
#"Extracted Values2" = Table.TransformColumns(#"Grouped Rows", {"Names", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
#"Removed Columns" = Table.RemoveColumns(#"Extracted Values2",{"Index"})
in
#"Removed Columns"

score 0 · Answer 2 · answered Aug 04 '17 at 18:03

It's not pretty, but if you want to accomplish this in DAX and not the query editor, you can use this formula; just replace "Table2" and "Names" with the names of your tables.

Names = IF(Table2[Selected Ids] = "BLANK", "BLANK",
IF(
    SEARCH(",", Table2[Selected Ids], 1, -1) = -1,
    LOOKUPVALUE(Names[Name], Names[Id], Table2[Selected Ids]),

    CONCATENATE(
        CONCATENATE(LOOKUPVALUE(Names[Name], Names[Id],
            MID(Table2[Selected Ids], 1, SEARCH(",", Table2[Selected Ids], 1, LEN(Table2[Selected Ids])) - 1)
            ), ","),

        IF(
            SEARCH(",", Table2[Selected Ids],
                SEARCH(",", Table2[Selected Ids], 1, LEN(Table2[Selected Ids])) + 1,
                -1) = -1,

            LOOKUPVALUE(Names[Name], Names[Id],
                MID(Table2[Selected Ids], 
                    SEARCH(",", Table2[Selected Ids], 1, LEN(Table2[Selected Ids])) + 1, 
                    LEN(Table2[Selected Ids]) - SEARCH(",", Table2[Selected Ids], 1, LEN(Table2[Selected Ids]))
                    )
                ),

            CONCATENATE(
                CONCATENATE(LOOKUPVALUE(Names[Name], Names[Id],
                    MID(Table2[Selected Ids], 
                        SEARCH(",", Table2[Selected Ids], 1, LEN(Table2[Selected Ids])) + 1, 
                        SEARCH(",", Table2[Selected Ids], SEARCH(",", Table2[Selected Ids], 1, LEN(Table2[Selected Ids])) + 1, LEN(Table2[Selected Ids])) - SEARCH(",", Table2[Selected Ids], 1, 0) - 1
                        )
                    ), ","),

                LOOKUPVALUE(Names[Name], Names[Id],
                    MID(Table2[Selected Ids], 
                    SEARCH(",", Table2[Selected Ids], SEARCH(",", Table2[Selected Ids], 1, LEN(Table2[Selected Ids])) + 1, LEN(Table2[Selected Ids])) + 1, 
                    LEN(Table2[Selected Ids]) - SEARCH(",", Table2[Selected Ids], SEARCH(",", Table2[Selected Ids], 1, LEN(Table2[Selected Ids])) + 1, LEN(Table2[Selected Ids]))
                    )
                )
            )
        )
    )
)

This formula will only handle up to two commas. If you want to extend it to handle more, replace the last LOOKUPVALUE with another IF expanding each of the SEARCH functions to check for a third comma.

Replace in Power BI

2 Answers2