The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredCompete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.
I've got a table with the following information as below:
Date | Fruit | Amount Sold | Sale Amount |
201812 | Apples | 150 | 75 |
201901 | Apples | 50 | 25 |
201902 | Apples | 100 | 50 |
201812 | Pears | 70 | 35 |
201901 | Pears | 80 | 40 |
201902 | Pears | 110 | 55 |
I need it represented as below with the differences on a row - not as a column
Date | Fruit | Amount Sold | Sale Amount |
201812 | Apples | 150 | 75 |
201902 | Apples | 100 | 50 |
AppleDifference | -50 | -25 | |
201812 | Pears | 70 | 35 |
201902 | Pears | 110 | 55 |
PearDifference | 40 | 20 |
Any ideas?
Solved! Go to Solution.
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 !
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")
A | B | C | |
1 | part | install date | started |
2 | PAU-0101 | 26/03/2019 05:17 | yes |
3 | PAU-0101 | 07/04/2019 18:56 | No |
4 | PAU-0101 | 07/04/2019 19:56 | yes |
5 | PAU-0102 | 26/03/2019 05:17 | yes |
6 | PAU-0102 | 07/04/2019 18:56 | No |
7 | PAU-0102 | 07/04/2019 19:56 | yes |
HI- unfortuantely we cant access onedrive from work. Please put the solution here. Thanks
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 !