Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi all, I have a table that contains many fields, one of which is List of Orders. This contains a list, where each value is an order. So for example:
And the lists might look like
List 1: {"A", "B"}
List 2: {"C"}
List 3: {"A", "B", "D"}
List 4: {"A", "B", "E"}
List 5: {"C", "D"}
I'd like to add a column [Resubmitted Orders] that says, for each list in [List of Orders], determine if ALL the orders in that list are in another single list. So for the above, List 1 and List 2 would both be TRUE because all of the values in List 1 are in List 3/List 4, and all of the values in List 2 are in List 5.
Thanks in advance for all of your help!
Solved! Go to Solution.
Looks like I was able to figure this one out myself 🙂
AddNewest =
Table.AddColumn(#"Reordered Columns", "Duplicated Orders", each
let
FocusList = [List of Orders],
FocusID = [Refresh ID],
ExcludeRow = Table.SelectRows(#"Reordered Columns", each [Refresh ID] <> FocusID),
ListOfLists = ExcludeRow[List of Orders],
TableOfLists =
Table.AddColumn(
Table.FromList(ListOfLists, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
"Match",
each List.ContainsAll([Column1], FocusList),
type logical
)
in
TableOfLists
),
#"Added Custom2" = Table.AddColumn(AddNewest, "Duplicates", each
Table.RowCount(Table.SelectRows([Duplicated Orders], each [Match] = true))),
#"Filtered Rows2" = Table.SelectRows(#"Added Custom2", each ([Duplicates] = 0)),
#"Removed Columns2" = Table.RemoveColumns(#"Filtered Rows2",{"Duplicated Orders", "Duplicates", "List of Orders"})
in
#"Removed Columns2"
Looks like I was able to figure this one out myself 🙂
AddNewest =
Table.AddColumn(#"Reordered Columns", "Duplicated Orders", each
let
FocusList = [List of Orders],
FocusID = [Refresh ID],
ExcludeRow = Table.SelectRows(#"Reordered Columns", each [Refresh ID] <> FocusID),
ListOfLists = ExcludeRow[List of Orders],
TableOfLists =
Table.AddColumn(
Table.FromList(ListOfLists, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
"Match",
each List.ContainsAll([Column1], FocusList),
type logical
)
in
TableOfLists
),
#"Added Custom2" = Table.AddColumn(AddNewest, "Duplicates", each
Table.RowCount(Table.SelectRows([Duplicated Orders], each [Match] = true))),
#"Filtered Rows2" = Table.SelectRows(#"Added Custom2", each ([Duplicates] = 0)),
#"Removed Columns2" = Table.RemoveColumns(#"Filtered Rows2",{"Duplicated Orders", "Duplicates", "List of Orders"})
in
#"Removed Columns2"