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.
Hallo,
I have a table with two columns and I want to add a third column which should show "true" if the value of the first column exists somewhere (not only in the same row) in the second column otherwise "false". I tried to use List.Contain but this works only with values in the same row. Maybe a short example what I mean:
C1 C2 C3
A A True
A B True because A exist in row 1 of C2
C B False
B D True because B exists in row 2 and 3 in C2
The examples in row 1 and 3 work fine because they are in the same row. Is there a solution for row 2 and 4????
Thank you for your support
Fred
Solved! Go to Solution.
Hi @Anonymous
This should perform better, however the final output is different table structure.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSAeJYHQjLCcxyhrOcgCwXpdhYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Col1 = _t, Col2 = _t]), tbl = Table.TransformColumnTypes(Source,{{"Col1", type text}, {"Col2", type text}}), col1 = List.Distinct(tbl[Col1]), #"Converted to Table" = Table.FromList(col1, Splitter.SplitByNothing(), null, null, ExtraValues.Error), #"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Col1"}}), #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Col1", type text}}), col2 = List.Distinct(tbl[Col2]), #"Converted to Table1" = Table.FromList(col2, Splitter.SplitByNothing(), null, null, ExtraValues.Error), #"Renamed Columns1" = Table.RenameColumns(#"Converted to Table1",{{"Column1", "Col2"}}), #"Changed Type1" = Table.TransformColumnTypes(#"Renamed Columns1",{{"Col2", type text}}), #"Merged Queries" = Table.NestedJoin(#"Changed Type1", {"Col2"}, #"Changed Type", {"Col1"}, "Col1", JoinKind.FullOuter), #"Expanded Col1" = Table.ExpandTableColumn(#"Merged Queries", "Col1", {"Col1"}, {"Col1"}), #"Added Custom" = Table.AddColumn(#"Expanded Col1", "Check", each [Col2]=[Col1], type logical) in #"Added Custom"
Hope this helps.
Mariusz
Hi @Anonymous,
Ypu can use the below M code
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSAeJYHQjLCcxyhrOcgCwXpdhYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Col1 = _t, Col2 = _t]), tbl = Table.TransformColumnTypes(Source,{{"Col1", type text}, {"Col2", type text}}), compaireValues = List.Distinct(tbl[Col2]), #"Added Custom" = Table.AddColumn(tbl, "Custom", each List.ContainsAny({[Col1]}, compaireValues)) in #"Added Custom"
Hope this Helps!
Mariusz
Hi Mariusz,
Thank you for your answer. There is a good and a bad message. First the good one, the code works. The bad one, the report has about 14000 rows, to load the query in excel takes about 20 minutes. My notebook is not the newest one with the fastest processor but 20 minutes is heavy. On the other hand excel compares every row with the comparerlist row by row 14000 times which is a lot but 20 Minutes for that, there might be another reason for that long runtime, any idea??
Best regards and thank you for your support
Fred
Hi @Anonymous
This should perform better, however the final output is different table structure.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSAeJYHQjLCcxyhrOcgCwXpdhYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Col1 = _t, Col2 = _t]), tbl = Table.TransformColumnTypes(Source,{{"Col1", type text}, {"Col2", type text}}), col1 = List.Distinct(tbl[Col1]), #"Converted to Table" = Table.FromList(col1, Splitter.SplitByNothing(), null, null, ExtraValues.Error), #"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Col1"}}), #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Col1", type text}}), col2 = List.Distinct(tbl[Col2]), #"Converted to Table1" = Table.FromList(col2, Splitter.SplitByNothing(), null, null, ExtraValues.Error), #"Renamed Columns1" = Table.RenameColumns(#"Converted to Table1",{{"Column1", "Col2"}}), #"Changed Type1" = Table.TransformColumnTypes(#"Renamed Columns1",{{"Col2", type text}}), #"Merged Queries" = Table.NestedJoin(#"Changed Type1", {"Col2"}, #"Changed Type", {"Col1"}, "Col1", JoinKind.FullOuter), #"Expanded Col1" = Table.ExpandTableColumn(#"Merged Queries", "Col1", {"Col1"}, {"Col1"}), #"Added Custom" = Table.AddColumn(#"Expanded Col1", "Check", each [Col2]=[Col1], type logical) in #"Added Custom"
Hope this helps.
Mariusz
Hi Greg,
Thank you for your reply but there is no answer only a link to ImkeF. Maybe I should follow this link or something went wrong.
Fred
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 |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
93 | |
84 | |
66 |