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.
Hi everyone! I have a table that has two columns that contain lists of text items and would like to add a third column that would display whether the lists match:
The [List X]{0} differs from [List X]{1} and [List X]{2}, same for [List Y] records.
So I am trying to create a third column that returns for each record "True" if each list member in List X matches the corresponding list member in List Y. I'm not trying to compare the Distinct Members (e.g. using List.Compare). Rather, if [List X]{0} = {1,2,3} and [List Y]{0} = {3,2,1}, then the [New Column]{0} should have "False".
Can someone advise the M code to add this column? I've tried List.Accumulate, but could not figure it out.
Thanks!
Jeff
Solved! Go to Solution.
Hello
as lists can be compared by themselves, meaning {}={} you can use the following syntax
=Table.AddColumn(YourTable, "ListX=ListY", each [List X] = [List Y])
have fun
Jimmy
Hello
as lists can be compared by themselves, meaning {}={} you can use the following syntax
=Table.AddColumn(YourTable, "ListX=ListY", each [List X] = [List Y])
have fun
Jimmy
Wow, worked like a charm! i was definitely overthinking things. I guess I'll put off fully understanding List.Accumulate to another day - no worries!
Thanks.
Well that was easy. I did not even think to do that. Thanks for the tip!
The code below should handle it.
List.Accumulate(List.Positions([List X]), true, (s,c) => s and ([List X]{c} = [List Y]{c}))
Unfortunately, i'm getting this error:
"Expression.Error: There is an unknown identifier. Did you use the [field] shorthand for a _[field] outside of an 'each' expression?"
This happens with or without a Table.Addcolumn wrapper. I get it when i run any of the nested functions. When I adapt the code to this:
PositionList = List.Positions(Table.Column(ListInputTable,"List X"){0}),
AddCheckCol = Table.AddColumn(ListInputTable,"Duplicate", each
List.Accumulate(PositionList, true, (s,c) => s and (Table.Column(ListInputTable,"List X"){c} =
Table.Column(ListInputTable,"List Y"){c}))
),
I get a table of the desired structure:, but the result is not correct (should be 'TRUE' in Row 2):
Any ideas?
Thanks for the assist,
Jeff
What is the data in the lists?
The table is a cross join of three lists: DataColA, B and C below. List "DataColA" = List "DataColC" <> List "DataColB". Essentially, this is supposed to check to see if any column in the input table is a duplicate of any other column in that table.
So there should be a True for Row 2 record and a False for the others:
Lists:
DataColA | DataColB | DataColC |
Ben | Test | Ben |
Dan | Dan | Dan |
Norm | Norm | Norm |
Valerie | Valerie | Valerie |
References in data table: | ||
Record | List X | List Y |
1 | DataColA | DataColB |
2 | DataColA | DataColC |
3 | DataColB | DataColC |
Let me know if this is what you are looking for. I am sure that my creation of the cross lists is too cumbersome, but it shows the list.accumulate in action.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WckrNU9JRCkktLgFSIE6sTrSSSyJIEEGCxPzyi3KBXGQKJByWmJNalJkKFMJkxcYCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [DataColA = _t, DataColB = _t, DataColC = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"DataColA", type text}, {"DataColB", type text}, {"DataColC", type text}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {}, {{"A", each List.Combine({[DataColA]}), type list}, {"B", each List.Combine({[DataColB]}), type list},{"C", each List.Combine({[DataColC]}), type list}}),
RemoveC = Table.RemoveColumns(#"Grouped Rows",{"C"}),
ListAB = Table.RenameColumns(RemoveC,{{"A", "List X"}, {"B", "List Y"}}),
RemoveB = Table.RemoveColumns(#"Grouped Rows",{"B"}),
ListAC = Table.RenameColumns(RemoveB,{{"A", "List X"}, {"C", "List Y"}}),
RemoveA = Table.RemoveColumns(#"Grouped Rows",{"A"}),
ListBC = Table.RenameColumns(RemoveA,{{"B", "List X"}, {"C", "List Y"}}),
Combine = ListBC & ListAC & ListAB,
AddCheckColumn = Table.AddColumn(Combine, "Custom", each List.Accumulate(List.Positions([List X]), true, (s,c) => s and ([List X]{c} = [List Y]{c})))
in
AddCheckColumn
For the record, this indeed worked, using my "tblInput" as the Source. And I'm going to have to understand what you did with that initial JSON function! My guess is that you scraped the post and used some sort of converter to generate the binary? Or perhaps it was a pointer to the post itself? In any case, thanks for the help...
You are giving me too much credit. I just copied and pasted the data from the browser into Power BI as a separate table 😊.
Paste into here
From here, the rest of the query turned this data into your lists.
Regards,
Mike
OK Gentlemen, this is my final note on the post. Thanks again for all your help.
Problem: Dimensional entities in data warehouse have fields/columns that seem to be duplicates but can't say for sure. How can we test? Can be hard to say when table runs into thousands or more rows with few and subtle differences.
Solution: (Thanks to you) Connect to the data, remove all columns except for those of interest, then use this function:
Hello
don't get exactly what you want.
You have a list and want to check wheter in the list are duplicated entries?
What is your data structure? Maybe it would best to create a new thread.
Jimmy
Hi Jimmy, i was simply posting my original problem and the solution that i developed with your help. Just to provide some context, code and example input/output for anyone that googles the blog with a similar need. No need for any further assistance. Thanks again,
Jeff
oops...
for this thread I didn't use any Binary-function.
simple wrote the sulution without testing.
It was another user that posted the binary-solution 🙂
Exactly... used the build-in data converter for tables.
I could have use the #table function as well to enable data changes in the query itself
have a nice day
Jimmy
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 |
---|---|
102 | |
48 | |
19 | |
13 | |
11 |