Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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
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
just brilliant sevenhills and so quick response !!!
Many thanks
Stéphane
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
100 | |
80 | |
63 | |
58 |
User | Count |
---|---|
148 | |
111 | |
94 | |
84 | |
67 |