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

Compete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.

Reply
Madelein
Frequent Visitor

Row Calculations

I've got a table with the following information as below:

DateFruitAmount SoldSale Amount
201812Apples15075
201901Apples5025
201902Apples10050
201812Pears7035
201901Pears8040
201902Pears11055

 

I need it represented as below with the differences on a row - not as a column

DateFruitAmount SoldSale Amount
201812Apples15075
201902Apples10050
 AppleDifference-50-25
201812Pears7035
201902Pears11055
 PearDifference4020

 

Any ideas?

1 ACCEPTED SOLUTION
Anonymous
Not applicable

1) Query 1

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwtDA0UtJRciwoyEktBjIMTQ2ApLmpUqwOWNrSwBBZGixrhCSLqtnAAKIGKg0xOyA1sQgkaw6SNEYzGSZpAZI0MUA1GCZpaAg2F6g1FgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Date = _t, Fruit = _t, #"Amount Sold" = _t, #"Sale Amount" = _t]),
    ChangedType = Table.TransformColumnTypes(Source,{{"Date", type text}, {"Fruit", type text}, {"Amount Sold", Int64.Type}, {"Sale Amount", Int64.Type}}),
    GroupedRows = Table.Group(ChangedType, {"Fruit"}, {{"Count", each Table.AddIndexColumn(_, "ID",1,1), type table [Date=number, Fruit=text, Amount Sold=number, Sale Amount=number]}}),
    ExpandedCount = Table.ExpandTableColumn(GroupedRows, "Count", {"Date", "Amount Sold", "Sale Amount","ID"}, {"Date", "Amount Sold", "Sale Amount","ID"}),
    ChangedType1 = Table.TransformColumnTypes(ExpandedCount,{{"Date", type text}}),
    Amount1 = Table.AddColumn(ChangedType1, "Amount1", each if [ID] = 1 then [Amount Sold] else [Amount Sold] * -1),
    Sales1 = Table.AddColumn(Amount1, "Sales1", each if [ID] = 1 then [Sale Amount] else [Sale Amount] * -1),
    #"Removed Other Columns" = Table.SelectColumns(Sales1,{"Fruit", "Date", "Amount Sold", "Sale Amount"}),
    #"Appended Query" = Table.Combine({#"Removed Other Columns", SalesDump2}),
    #"Reordered Columns" = Table.ReorderColumns(#"Appended Query",{"Date", "Fruit", "Amount Sold", "Sale Amount"}),
    SortedRows = Table.Sort(#"Reordered Columns",{{"Fruit", Order.Ascending}})
in
    SortedRows

2) Query2 (Untick Enable Load, we only need it for support)

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwtDA0UtJRciwoyEktBjIMTQ2ApLmpUqwOWNrSwBBZGixrhCSLqtnAAKIGKg0xOyA1sQgkaw6SNEYzGSZpAZI0MUA1GCZpaAg2F6g1FgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Date = _t, Fruit = _t, #"Amount Sold" = _t, #"Sale Amount" = _t]),
    ChangedType = Table.TransformColumnTypes(Source,{{"Date", type text}, {"Fruit", type text}, {"Amount Sold", Int64.Type}, {"Sale Amount", Int64.Type}}),
    GroupedRows = Table.Group(ChangedType, {"Fruit"}, {{"Count", each Table.AddIndexColumn(_, "ID",1,1), type table [Date=number, Fruit=text, Amount Sold=number, Sale Amount=number]}}),
    ExpandedCount = Table.ExpandTableColumn(GroupedRows, "Count", {"Date", "Amount Sold", "Sale Amount","ID"}, {"Date", "Amount Sold", "Sale Amount","ID"}),
    Amount1 = Table.AddColumn(ExpandedCount, "Amount1", each if [ID] = 1 then [Amount Sold] else [Amount Sold] * -1),
    Sales1 = Table.AddColumn(Amount1, "Sales1", each if [ID] = 1 then [Sale Amount] else [Sale Amount] * -1),
    Grouped_Rows = Table.Group(Sales1, {"Fruit"}, {{"Amount Sold", each List.Sum([Amount1]), type number}, {"Sale Amount", each List.Sum([Sales1]), type number}}),
    HelperCol = Table.AddColumn(Grouped_Rows, "Fruits", each [Fruit] & "Difference"),
    RemovedOtherColumns = Table.SelectColumns(HelperCol,{"Fruits", "Amount Sold", "Sale Amount"}),
    RenamedColumns = Table.RenameColumns(RemovedOtherColumns,{{"Fruits", "Fruit"}}),
    ChangedType1 = Table.TransformColumnTypes(RenamedColumns,{{"Fruit", type text}})
in
    ChangedType1

 

3) That's it !

 

View solution in original post

4 REPLIES 4
edgarjb-68
Frequent Visitor

I have a question on rows calculation on Power BI. How to calculate rows on a table? I wany to create a column called "started" as below. For each row of this column I need the results of this excel like formula on "started" cell C2: =IF(AND(B4=B3;C4-C3<1);"No";"yes")

 

 ABC
1partinstall datestarted
2PAU-010126/03/2019 05:17yes
3PAU-010107/04/2019 18:56No
4PAU-010107/04/2019 19:56yes
5PAU-010226/03/2019 05:17yes
6PAU-010207/04/2019 18:56No
7PAU-010207/04/2019 19:56yes
Madelein
Frequent Visitor

HI- unfortuantely we cant access onedrive from work. Please put the solution here. Thanks

Anonymous
Not applicable

1) Query 1

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwtDA0UtJRciwoyEktBjIMTQ2ApLmpUqwOWNrSwBBZGixrhCSLqtnAAKIGKg0xOyA1sQgkaw6SNEYzGSZpAZI0MUA1GCZpaAg2F6g1FgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Date = _t, Fruit = _t, #"Amount Sold" = _t, #"Sale Amount" = _t]),
    ChangedType = Table.TransformColumnTypes(Source,{{"Date", type text}, {"Fruit", type text}, {"Amount Sold", Int64.Type}, {"Sale Amount", Int64.Type}}),
    GroupedRows = Table.Group(ChangedType, {"Fruit"}, {{"Count", each Table.AddIndexColumn(_, "ID",1,1), type table [Date=number, Fruit=text, Amount Sold=number, Sale Amount=number]}}),
    ExpandedCount = Table.ExpandTableColumn(GroupedRows, "Count", {"Date", "Amount Sold", "Sale Amount","ID"}, {"Date", "Amount Sold", "Sale Amount","ID"}),
    ChangedType1 = Table.TransformColumnTypes(ExpandedCount,{{"Date", type text}}),
    Amount1 = Table.AddColumn(ChangedType1, "Amount1", each if [ID] = 1 then [Amount Sold] else [Amount Sold] * -1),
    Sales1 = Table.AddColumn(Amount1, "Sales1", each if [ID] = 1 then [Sale Amount] else [Sale Amount] * -1),
    #"Removed Other Columns" = Table.SelectColumns(Sales1,{"Fruit", "Date", "Amount Sold", "Sale Amount"}),
    #"Appended Query" = Table.Combine({#"Removed Other Columns", SalesDump2}),
    #"Reordered Columns" = Table.ReorderColumns(#"Appended Query",{"Date", "Fruit", "Amount Sold", "Sale Amount"}),
    SortedRows = Table.Sort(#"Reordered Columns",{{"Fruit", Order.Ascending}})
in
    SortedRows

2) Query2 (Untick Enable Load, we only need it for support)

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwtDA0UtJRciwoyEktBjIMTQ2ApLmpUqwOWNrSwBBZGixrhCSLqtnAAKIGKg0xOyA1sQgkaw6SNEYzGSZpAZI0MUA1GCZpaAg2F6g1FgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Date = _t, Fruit = _t, #"Amount Sold" = _t, #"Sale Amount" = _t]),
    ChangedType = Table.TransformColumnTypes(Source,{{"Date", type text}, {"Fruit", type text}, {"Amount Sold", Int64.Type}, {"Sale Amount", Int64.Type}}),
    GroupedRows = Table.Group(ChangedType, {"Fruit"}, {{"Count", each Table.AddIndexColumn(_, "ID",1,1), type table [Date=number, Fruit=text, Amount Sold=number, Sale Amount=number]}}),
    ExpandedCount = Table.ExpandTableColumn(GroupedRows, "Count", {"Date", "Amount Sold", "Sale Amount","ID"}, {"Date", "Amount Sold", "Sale Amount","ID"}),
    Amount1 = Table.AddColumn(ExpandedCount, "Amount1", each if [ID] = 1 then [Amount Sold] else [Amount Sold] * -1),
    Sales1 = Table.AddColumn(Amount1, "Sales1", each if [ID] = 1 then [Sale Amount] else [Sale Amount] * -1),
    Grouped_Rows = Table.Group(Sales1, {"Fruit"}, {{"Amount Sold", each List.Sum([Amount1]), type number}, {"Sale Amount", each List.Sum([Sales1]), type number}}),
    HelperCol = Table.AddColumn(Grouped_Rows, "Fruits", each [Fruit] & "Difference"),
    RemovedOtherColumns = Table.SelectColumns(HelperCol,{"Fruits", "Amount Sold", "Sale Amount"}),
    RenamedColumns = Table.RenameColumns(RemovedOtherColumns,{{"Fruits", "Fruit"}}),
    ChangedType1 = Table.TransformColumnTypes(RenamedColumns,{{"Fruit", type text}})
in
    ChangedType1

 

3) That's it !

 

Anonymous
Not applicable

Try this!

 

Solution

 

Please mark this resolved if the solution suits your requirement.

Helpful resources

Announcements
August Power BI Update Carousel

Power BI Monthly Update - August 2025

Check out the August 2025 Power BI update to learn about new features.

August 2025 community update carousel

Fabric Community Update - August 2025

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

Top Solution Authors