Forum Discussion
Need to compare rows of tables dynamically
If anyone else is trying to do something like this, here's the code from the point that I got all my data into a standardized table (the source) to the point that I aggregated the data for each category and person. I'm still totally open to suggestions for better ways.
#"Data Columns" = List.Skip(Table.ColumnNames(Source)), #"Changed to Number" = Table.TransformColumnTypes(Source, List.Transform(#"Data Columns", each {_, type number})), #"Replaced Nulls" = Table.ReplaceValue(#"Changed to Number", null, 0, Replacer.ReplaceValue, #"Data Columns"), #"Inserted Text After Delimiter" = Table.AddColumn(#"Replaced Nulls", "Type", each Text.AfterDelimiter([Temp Column], ": "), type text), #"Extract DP1" = Table.TransformColumns(Table.SelectRows(#"Inserted Text After Delimiter", each ([Type] = "DP1")), List.Transform(#"Data Columns", each {_, each _*-1})), #"Extract DP2" = Table.SelectRows(#"Inserted Text After Delimiter", each [Type] = "DP2"), #"Re-combine" = Table.Combine({#"Extract DP1", #"Extract DP2"}), #"Inserted Text Before Delimiter" = Table.AddColumn(#"Re-combine", "Category", each Text.BeforeDelimiter([Temp Column], ":"), type text), #"Removed Columns" = Table.RemoveColumns(#"Inserted Text Before Delimiter", {"Temp Column", "Type"}), #"Grouped Rows" = Table.Group(#"Removed Columns", {"Category"}, {{"Group by Category", each _, type table}}), #"Aggregated Columns" = Table.AggregateTableColumn(#"Grouped Rows", "Group by Category", List.Transform(#"Data Columns", each {_, List.Sum, _})), ...
The first sheet is what it looks like in PQ at the point I'm getting stuck. The second sheet is what I'd like to get out of it. (I've since replaced nulls with 0s and converted the dates to numbers for easier comparison.)
I did finally come up with a solution, which involved splitting on the Type column and switching the sign of one of them, then putting them back together and aggregating on Category with List.Sum.
If anyone else is trying to do something like this, here's the code from the point that I got all my data into a standardized table (the source) to the point that I aggregated the data for each category and person. I'm still totally open to suggestions for better ways.
#"Data Columns" = List.Skip(Table.ColumnNames(Source)),
#"Changed to Number" = Table.TransformColumnTypes(Source, List.Transform(#"Data Columns", each {_, type number})),
#"Replaced Nulls" = Table.ReplaceValue(#"Changed to Number", null, 0, Replacer.ReplaceValue, #"Data Columns"),
#"Inserted Text After Delimiter" = Table.AddColumn(#"Replaced Nulls", "Type", each Text.AfterDelimiter([Temp Column], ": "), type text),
#"Extract DP1" = Table.TransformColumns(Table.SelectRows(#"Inserted Text After Delimiter", each ([Type] = "DP1")), List.Transform(#"Data Columns", each {_, each _*-1})),
#"Extract DP2" = Table.SelectRows(#"Inserted Text After Delimiter", each [Type] = "DP2"),
#"Re-combine" = Table.Combine({#"Extract DP1", #"Extract DP2"}),
#"Inserted Text Before Delimiter" = Table.AddColumn(#"Re-combine", "Category", each Text.BeforeDelimiter([Temp Column], ":"), type text),
#"Removed Columns" = Table.RemoveColumns(#"Inserted Text Before Delimiter", {"Temp Column", "Type"}),
#"Grouped Rows" = Table.Group(#"Removed Columns", {"Category"}, {{"Group by Category", each _, type table}}),
#"Aggregated Columns" = Table.AggregateTableColumn(#"Grouped Rows", "Group by Category", List.Transform(#"Data Columns", each {_, List.Sum, _})),
...
v-kkf-msftCommunity Support
Hi scampbll ,
I'm glad you've solved the problem. Here is just another option provided.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wck4sSU3PL6pUMLRScAlQMFTSUTLUN9Q3MjAyAjKN9I1gTGN9YxjTRN8ExkRoB3LA+mN1MAw1wmqoMZCZV5qTA6RMEZLoBhqhGmgEd6UpxBEgU8z0zWBMJFdCzUZoxepCI7gLoY5AMtCIKAOBLowFAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Temp Column" = _t, #"Person 1" = _t, #"Person 2" = _t, #"Person 3" = _t, #"Person 4" = _t, Category = _t, Type = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Temp Column", type text}, {"Person 1", type date}, {"Person 2", type date}, {"Person 3", type date}, {"Person 4", type date}, {"Category", type text}, {"Type", type text}}), #"Replaced Value" = Table.ReplaceValue(#"Changed Type",null,#date(1900, 1, 1),Replacer.ReplaceValue,{"Person 1","Person 2","Person 3","Person 4"}), #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Replaced Value", {"Temp Column", "Category", "Type"}, "Name", "Value"), #"Grouped Rows" = Table.Group(#"Unpivoted Columns", {"Category", "Name"}, {{"Value", each Duration.Days([Value]{0} - [Value]{1}) , type date}}), #"Grouped Rows1" = Table.Group(#"Grouped Rows", {"Category"}, {{"Count", each Record.FromTable(_)}}), #"Expanded Count" = Table.ExpandRecordColumn(#"Grouped Rows1", "Count", {"Person 1", "Person 2", "Person 3", "Person 4"}, {"Person 1", "Person 2", "Person 3", "Person 4"}) in #"Expanded Count"Best Regards,
Winniz