Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
scampbll
New Member

Need to compare rows of tables dynamically

I'm trying to compare information from two reports. This involves 20-30 categories for up to 800 people. Both aspects are dynamic, but the categories are more easily pinned down than the people.

 

I've gotten as far as isolating the data I need and grouping it into subtables. There's one table per category and each has two rows, one for each data point. The people are the columns of each table.

 

What I need to know is the difference between the two data points (dates, actually) for each person. I can get a simple equal/not equal by aggregating with List.IsDistinct. That's helpful, but it would be much more helpful if I could get the actual difference between the two.

 

I've spent way too much time trying to solve this problem. I've tried doing an indexed-based comparison rather than grouping, I've tried using List.First and List.Last to extract the values, but I get stuck when I try to scale things up and do it dynamically rather than one column or table at a time. My grasp of the each and _ syntax isn't quite there yet.

 

Thanks for any suggestions.

1 ACCEPTED SOLUTION

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, _})),
...

 

 

View solution in original post

6 REPLIES 6
HotChilli
Super User
Super User

Great. Post a link here and I'll have a look

Mockup file 

 

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, _})),
...

 

 

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"

vkkfmsft_0-1657866042414.png

 

Best Regards,
Winniz

scampbll
New Member

Thanks. I can't share my actual data but I made a mockup. Should I link it or wait til I can load it into the post?

HotChilli
Super User
Super User

Please post some sample data (not pictures) and show the desired result

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors