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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Astrua
Regular Visitor

Finding the Delta between 2 nested tables

Hi All,

 

I have a list of data with 2 nested tables with before and after process data. How do I write the code to find the delta between the before and after table? row 1 and column 1 of [after] table subtract row 1 and column 1 of [before] table respectively for the rest of the data.

IDbeforeafter
1TableTable
2TableTable
3TableTable
4TableTable

The data in the nested table is like this

Before:

0.05220.04760.04390.0424
0.03560.03160.02860.0264
0.02210.01910.01620.0136
0.010.00790.00540.0033
-0.0008-0.0011-0.003-0.0051

After:

-0.098-0.0696-0.00040.0482
-0.071-0.00650.0320.0284
-0.00230.03230.020.0004
0.0360.023-0.0012-0.0203
0.02580.0013-0.0191-0.037
1 ACCEPTED SOLUTION
lbendlin
Super User
Super User

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlSK1YlWMgKTxmDSRCk2FgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "before", each Before),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "after", each After),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Column1", each [after]{[ID]-1}[Column1]-[before]{[ID]-1}[Column1]),
    #"Added Custom3" = Table.AddColumn(#"Added Custom2", "Column2", each [after]{[ID]-1}[Column2]-[before]{[ID]-1}[Column2]),
    #"Added Custom4" = Table.AddColumn(#"Added Custom3", "Column3", each [after]{[ID]-1}[Column3]-[before]{[ID]-1}[Column3]),
    #"Added Custom5" = Table.AddColumn(#"Added Custom4", "Column4", each [after]{[ID]-1}[Column4]-[before]{[ID]-1}[Column4])
in
    #"Added Custom5"

lbendlin_0-1696375278379.png

 

 

 

View solution in original post

1 REPLY 1
lbendlin
Super User
Super User

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlSK1YlWMgKTxmDSRCk2FgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "before", each Before),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "after", each After),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Column1", each [after]{[ID]-1}[Column1]-[before]{[ID]-1}[Column1]),
    #"Added Custom3" = Table.AddColumn(#"Added Custom2", "Column2", each [after]{[ID]-1}[Column2]-[before]{[ID]-1}[Column2]),
    #"Added Custom4" = Table.AddColumn(#"Added Custom3", "Column3", each [after]{[ID]-1}[Column3]-[before]{[ID]-1}[Column3]),
    #"Added Custom5" = Table.AddColumn(#"Added Custom4", "Column4", each [after]{[ID]-1}[Column4]-[before]{[ID]-1}[Column4])
in
    #"Added Custom5"

lbendlin_0-1696375278379.png

 

 

 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors