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
William_Moreno
Helper II
Helper II

difference between two rows in the power query (m-language)

Hi everyone, I need help.

difference between two rows in the power query (m-language)

table - 1

dateunitskuvalue
2021-07-01Berlim10025510.5
2021-07-01Rome10025511.5
2021-07-01New York10025512.5

2021-08-01

Berlim10025513.0
2021-08-01Rome10025514.0
2021-08-01New York10025517.0

 

expected results 

dateunitskuvaluediference
2020-07-01Berlim10025510.50
2020-07-01Rome10025511.50
2020-07-01New York10025512.50

2021-08-01

Berlim10025513.02.5
2021-08-01Rome10025515.03.5
2021-08-01New York10025518.05.5

 

Could you help?

Thanks in advanced!

1 ACCEPTED SOLUTION
v-jingzhang
Community Support
Community Support

Hi @William_Moreno 

 

Here is another method.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMtQ1MNc1MFTSUXJKLcrJzAUyDA0MjExNwQw9U6VYHTR1Qfm5qSiqDLGp8kstV4jML8pGUWmEotICp73GegaY6jDsxWIWVlstlGJjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [date = _t, unit = _t, sku = _t, value = _t]),
    #"Changed Type with Locale" = Table.TransformColumnTypes(Source, {{"date", type date}, {"value", type number}}, "en-US"),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type with Locale", "Index", 0, 1, Int64.Type),
    #"Grouped Rows" = Table.Group(#"Added Index", {"unit", "sku"}, {{"Groups", each _, type table [date=nullable text, unit=nullable text, sku=nullable text, value=nullable number]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Groups2", each Table.AddIndexColumn([Groups], "sort", 0, 1, Int64.Type)),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Groups2"}),
    #"Expanded GroupsIndex" = Table.ExpandTableColumn(#"Removed Other Columns", "Groups2", {"date", "unit", "sku", "value", "Index", "sort"}, {"date", "unit", "sku", "value", "Index", "sort"}),
    #"Added Custom1" = Table.AddColumn(#"Expanded GroupsIndex", "Difference", each if [sort] = 0 then 0 else [value] - Table.SelectRows(#"Expanded GroupsIndex", (x)=> x[unit]=[unit] and x[sku]=[sku] and x[sort]=[sort]-1){0}[value]),
    #"Sorted Rows" = Table.Sort(#"Added Custom1",{{"Index", Order.Ascending}}),
    #"Removed Columns" = Table.RemoveColumns(#"Sorted Rows",{"Index", "sort"})
in
    #"Removed Columns"

072601.jpg

 

Regards,
Community Support Team _ Jing
If this post helps, please Accept it as the solution to help other members find it.

View solution in original post

2 REPLIES 2
v-jingzhang
Community Support
Community Support

Hi @William_Moreno 

 

Here is another method.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMtQ1MNc1MFTSUXJKLcrJzAUyDA0MjExNwQw9U6VYHTR1Qfm5qSiqDLGp8kstV4jML8pGUWmEotICp73GegaY6jDsxWIWVlstlGJjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [date = _t, unit = _t, sku = _t, value = _t]),
    #"Changed Type with Locale" = Table.TransformColumnTypes(Source, {{"date", type date}, {"value", type number}}, "en-US"),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type with Locale", "Index", 0, 1, Int64.Type),
    #"Grouped Rows" = Table.Group(#"Added Index", {"unit", "sku"}, {{"Groups", each _, type table [date=nullable text, unit=nullable text, sku=nullable text, value=nullable number]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Groups2", each Table.AddIndexColumn([Groups], "sort", 0, 1, Int64.Type)),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Groups2"}),
    #"Expanded GroupsIndex" = Table.ExpandTableColumn(#"Removed Other Columns", "Groups2", {"date", "unit", "sku", "value", "Index", "sort"}, {"date", "unit", "sku", "value", "Index", "sort"}),
    #"Added Custom1" = Table.AddColumn(#"Expanded GroupsIndex", "Difference", each if [sort] = 0 then 0 else [value] - Table.SelectRows(#"Expanded GroupsIndex", (x)=> x[unit]=[unit] and x[sku]=[sku] and x[sort]=[sort]-1){0}[value]),
    #"Sorted Rows" = Table.Sort(#"Added Custom1",{{"Index", Order.Ascending}}),
    #"Removed Columns" = Table.RemoveColumns(#"Sorted Rows",{"Index", "sort"})
in
    #"Removed Columns"

072601.jpg

 

Regards,
Community Support Team _ Jing
If this post helps, please Accept it as the solution to help other members find it.

Jakinta
Solution Sage
Solution Sage

Try steps below.

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMtQ1MNc1MFTSUXJKLcrJzAUyDA0MjExNwQw9U6VYHTR1Qfm5qSiqDLGp8kstV4jML8pGUWmEotICp73GegaY6jDsxWIWVlstlGJjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [date = _t, unit = _t, sku = _t, value = _t]),
    Changed = Table.TransformColumnTypes(Source,{{"value", type number}}),
    Index = Table.AddIndexColumn(Changed, "Index", 0, 1, Int64.Type),
    Grouped = Table.Group(Index, {"unit"}, {{"Gr", each let l=_[value], n={0..List.Count(l)-1}, x=List.Transform(n, each if _=0 then 0 else l{_}-l{_-1} ) in Table.FromColumns(Table.ToColumns(_)&{x}, Table.ColumnNames(_)&{"difference"}), type table }}),
    Removed = Table.RemoveColumns(Grouped,{"unit"}),
    Expanded = Table.ExpandTableColumn(Removed, "Gr", Table.ColumnNames(Removed[Gr]{0})),
    Sorted = Table.Sort(Expanded,{{"Index", Order.Ascending}}),
    FINAL = Table.RemoveColumns(Sorted,{"Index"})
in
    FINAL

 

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