Power BI DAX - Split, Filter, Concatenate and Transform List to String...

[Anonymous]

Dear all,

I have two tables TD and TS in Salmon below and I need to build a table Result in background blue as below (a new table or a new column in Table TD). During this operation, for each row, I need to split the column R ("," as separator) and for each item of the list: - I need to filter it if in Table Result this item has a column S="D" (the case highlighted in blue, FC is found in Table TS with S="D")

- I need to append to the item the value found in column P

As example, the 1st row of Table TD = { "FA, "FB,FC" } becomes { "FA", "FB.P1.O" }. The word equal to FC is filtered out because in table TS, FC.S="D"

I was initially thinking about using Power Query but I don't see how to proceed as I think PowerQuery needs a single table to operate and I don't see how to merge TD and TS.

Now, I am thinking about DAX Split operations but I am not able to find _the_ formula...

Below the Tables TD and TS

TD

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnNU0lFyc9Jxc1aK1QFyXUBcV6XYWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [F = _t, R = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"F", type text}, {"R", type text}})
in
    #"Changed Type"

TS

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnNU0lHyB+IAI6VYHSDfCcY3hPCdgWwXEN8AwndBU++KpD4WAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [F = _t, S = _t, P = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"F", type text}, {"S", type text}, {"P", type text}})
in
    #"Changed Type"

and Expected Result

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnNU0lHyB+IAI6VYHSDfCcY3hPCdgWwXEN8AwndBU++KpD4WAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [F = _t, S = _t, P = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"F", type text}, {"S", type text}, {"P", type text}})
in
    #"Changed Type"

Thanks a lot for your help

Stéphane

[sevenhills · Accepted solution]

Try this in Power Query, to get blue color table in the image posted 

let
    Source = TD,
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(Source, {{"R", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "R"),
    #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"R", type text}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"R"}, TS, {"F"}, "TS", JoinKind.FullOuter),
    #"Expanded TS" = Table.ExpandTableColumn(#"Merged Queries", "TS", {"F", "S", "P"}, {"TS.F", "TS.S", "TS.P"}),
    #"Filtered Rows" = Table.SelectRows(#"Expanded TS", each ([R] <> null and [R] <> "") and ([TS.S] = "O")),
    #"Inserted Merged Column" = Table.AddColumn(#"Filtered Rows", "Merged", each Text.Combine({[TS.F], ".", [TS.P], ".", [TS.S]}), type text),
    #"Removed Columns" = Table.RemoveColumns(#"Inserted Merged Column",{"R", "TS.F", "TS.S", "TS.P"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Merged", "RU"}, {"F", "TT"}})
in
    #"Renamed Columns"

PS: FYI, your posted TS and Expected Result M Query is same. It took me a while they are same

[Anonymous]

just brilliant sevenhills and so quick response !!!

Many thanks

Stéphane