Conditional merge of 2 tables where one table has comma separated values

[KetD]

Conditional merge of table column when one column has comma separated values

10m ago

Hello,

I need help to do conditional merging of columns across 2 different tables. I have 2 tables in Power BI as follows.

Table1

Id	Name	Priority	Group
1	Name1	1	General
2	Name2	2	General
3	Name3	10	Express
4	Name4	21	Eco

Table2

Rate	Category	Name
10	A	Name1, Name3
12	B	Name3, Name4

The expected output is shown below. "Group" column should show the value by comparing the "priority" column in Table2 based on "Name" .

e.g. Name1 has priority 1 and Name2 has priority 2, so Group column will have value General as 1 is the highest priority (1 < 2).

Name3 has priority 10, Name4 has priority 21. so Group column will have value Express as 10 < 21.

Expected Table

Rate	Category	Name	Group
10	A	Name1	General
12	B	Name3	Express

Thank you!

[KetD]

Hello,

I need help to do conditional merging of columns across 2 different tables. I have 2 tables in Power BI as follows.

Table1

Id	Name	Priority	Group
1	Name1	1	General
2	Name2	2	General
3	Name3	10	Express
4	Name4	21	Eco

Table2

Rate	Category	Name
10	A	Name1, Name3
12	B	Name3, Name4

The expected output is shown below. "Group" column should show the value by comparing the "priority" column in Table2 based on "Name" .

e.g. Name1 has priority 1 and Name2 has priority 2, so Group column will have value General as 1 is the highest priority (1 < 2).

Name3 has priority 10, Name4 has priority 21. so Group column will have value Express as 10 < 21.

Expected Table

Rate	Category	Name	Group
10	A	Name1	General
12	B	Name3	Express

Thank you!

[lbendlin · Accepted solution]

Here is a rather pedestrian way of doing this. Note that I had to clean your comma separated list - it should not have a space after the comma.

Normally I would have used a custom column Generator with Table.AddColumn()

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjRQ0lFyBGK/xNxUQx0QaawUqwOUMAIKOkEljMESJkqxsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Rate = _t, Category = _t, Name = _t]),
#"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(Source, {{"Name", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Name"),
Source2 = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8kxR0lHyS8xNBVIBRZn5RZkllUCme1F+aYFSrE60kiFUHkSDsHtqXmpRYg5YzggqB6KN0OSMoXIg2tAASLhWFBSlFheDJU2gkiDaCGSqa3K+UmwsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t]),
#"Promoted Headers" = Table.PromoteHeaders(Source2, [PromoteAllScalars=true]),
#"Merged Queries" = Table.NestedJoin(#"Split Column by Delimiter", {"Name"}, #"Promoted Headers", {"Name"}, "Table1", JoinKind.LeftOuter),
#"Expanded Table1" = Table.ExpandTableColumn(#"Merged Queries", "Table1", {"Priority", "Group"}, {"Priority", "Group"}),
#"Sorted Rows" = Table.Sort(#"Expanded Table1",{{"Priority", Order.Ascending}}),
#"Removed Duplicates" = Table.Distinct(#"Sorted Rows", {"Rate"}),
#"Removed Other Columns" = Table.SelectColumns(#"Removed Duplicates",{"Rate", "Category", "Name", "Group"})
in
#"Removed Other Columns"