Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
hello folks,
I have the following data in the format shown below, I would like to group these columns together and extract the date (month) from the cloumn header (November would be similar to this too). My ideal goal is to create a line graph to show these values as a trend for the year.
October - Billed Unit | October - Billed Amount | October - Unit Variance | October - Amount Variance |
3,024 | $12,369.78 | 870 | $32,943.78 |
324 | $2,369.78 | 40 | $5734 |
55 | $1200 | 10 | $467 |
334 | $89,726.03 | 101 | $27,049.03 |
368 | $98,859.81 | 20 | $5,247.81 |
Any thoughts?
Solved! Go to Solution.
I don't know what exactly should be your output but maybe you want something like this:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jZBBDsUgCESv0hiXxCigwFka73+NVnDRzc93RZh5mSHcd7oIKnKCdOWGQMOK6LupVNcIwZhCO0YnrNyNfkgOsAu59YfwlN53X11OC5uHrPnTi/4oyWogOEolZ1o0ClS20A7ByBz+h2wK2q3ognCfDMgSyhE25wM=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"October - Billed Unit" = _t, #"October - Billed Amount" = _t, #"October - UnitVariance" = _t, #"October - Amount Variance" = _t, #"November - Billed Unit" = _t, #"November - Billed Amount" = _t, #"November - UnitVariance" = _t, #"November - Amount Variance" = _t]),
#"Demoted Headers" = Table.DemoteHeaders(Source),
#"Transposed Table" = Table.Transpose(#"Demoted Headers"),
#"Split Column by Delimiter" = Table.SplitColumn(#"Transposed Table", "Column1", Splitter.SplitTextByDelimiter(" - ", QuoteStyle.Csv), {"Month", "Category"}),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Split Column by Delimiter", {"Month", "Category"}, "Attribute", "Value"),
#"Added Index" = Table.AddIndexColumn(#"Unpivoted Other Columns", "Index", 1, 1, Int64.Type),
#"Changed Type" = Table.TransformColumnTypes(#"Added Index",{{"Value", type number}}, "en-US"),
#"Calculated Modulo" = Table.TransformColumns(#"Changed Type", {{"Index", each Number.Mod(_, List.Count(List.Distinct(#"Changed Type"[Attribute]))), type number}}),
#"Grouped Rows" = //Table.Pivot(#"Removed Other Columns", List.Distinct(#"Removed Other Columns"[Month]), "Month", "Index")
//Table.Pivot(#"Removed Other Columns", List.Distinct(#"Removed Other Columns"[Month]), "Month", "Index")
Table.Group(#"Calculated Modulo", {"Month", "Index"}, {{"Billed Unit", each Table.SelectRows(_, (a)=> a[Category] = "Billed Unit")[Value]{0}, Int64.Type}, {"Pivot", each Table.Pivot(_, List.Distinct([Category]), "Category", "Value", List.Sum), type table}}),
#"Removed Other Columns" = Table.SelectColumns(#"Grouped Rows",{"Billed Unit", "Pivot"}),
#"Expanded Pivot" = Table.ExpandTableColumn(#"Removed Other Columns", "Pivot", {"Month", "Billed Amount", "UnitVariance", "Amount Variance"}, {"Month", "Billed Amount", "UnitVariance", "Amount Variance"})
in
#"Expanded Pivot"
I don't know what exactly should be your output but maybe you want something like this:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jZBBDsUgCESv0hiXxCigwFka73+NVnDRzc93RZh5mSHcd7oIKnKCdOWGQMOK6LupVNcIwZhCO0YnrNyNfkgOsAu59YfwlN53X11OC5uHrPnTi/4oyWogOEolZ1o0ClS20A7ByBz+h2wK2q3ognCfDMgSyhE25wM=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"October - Billed Unit" = _t, #"October - Billed Amount" = _t, #"October - UnitVariance" = _t, #"October - Amount Variance" = _t, #"November - Billed Unit" = _t, #"November - Billed Amount" = _t, #"November - UnitVariance" = _t, #"November - Amount Variance" = _t]),
#"Demoted Headers" = Table.DemoteHeaders(Source),
#"Transposed Table" = Table.Transpose(#"Demoted Headers"),
#"Split Column by Delimiter" = Table.SplitColumn(#"Transposed Table", "Column1", Splitter.SplitTextByDelimiter(" - ", QuoteStyle.Csv), {"Month", "Category"}),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Split Column by Delimiter", {"Month", "Category"}, "Attribute", "Value"),
#"Added Index" = Table.AddIndexColumn(#"Unpivoted Other Columns", "Index", 1, 1, Int64.Type),
#"Changed Type" = Table.TransformColumnTypes(#"Added Index",{{"Value", type number}}, "en-US"),
#"Calculated Modulo" = Table.TransformColumns(#"Changed Type", {{"Index", each Number.Mod(_, List.Count(List.Distinct(#"Changed Type"[Attribute]))), type number}}),
#"Grouped Rows" = //Table.Pivot(#"Removed Other Columns", List.Distinct(#"Removed Other Columns"[Month]), "Month", "Index")
//Table.Pivot(#"Removed Other Columns", List.Distinct(#"Removed Other Columns"[Month]), "Month", "Index")
Table.Group(#"Calculated Modulo", {"Month", "Index"}, {{"Billed Unit", each Table.SelectRows(_, (a)=> a[Category] = "Billed Unit")[Value]{0}, Int64.Type}, {"Pivot", each Table.Pivot(_, List.Distinct([Category]), "Category", "Value", List.Sum), type table}}),
#"Removed Other Columns" = Table.SelectColumns(#"Grouped Rows",{"Billed Unit", "Pivot"}),
#"Expanded Pivot" = Table.ExpandTableColumn(#"Removed Other Columns", "Pivot", {"Month", "Billed Amount", "UnitVariance", "Amount Variance"}, {"Month", "Billed Amount", "UnitVariance", "Amount Variance"})
in
#"Expanded Pivot"
This is very promising. Can you update the sample source to point to an excel workbook instead of JSON doc?
Hi @icecold, you can do it:
I've updated previous code a little (Calculated Modulo step) - to be more dynamic.
Okay, I did as explained & it worked to an extent but that because I only posed a little snippet of my data. Here is a more accurate structure of the data:
Customer Code | Customer Description | Budget Unit | Budget Revenue | Monthly billing amount | Billed Unit-10/31 | Billed Amount-10/31 | Unit Variance-10/31 | Amount Variance-10/31 | Billed Unit-11/31 | Billed Amount-11/31 | Unit Variance-11/31 | Amount Variance-11/31 | ||
MSFT | Microsoft | 2,154 | $6953 | $5942 | 304 | $ 82,360 | 87 | $32,943.78 | 304 | $ 82,360 | 87 | $32,943.78 | ||
GOOG | 2,155 | $6953 | $5942 | 305 | $ 82,361 | 88 | $32,943.79 | 305 | $ 82,361 | 88 | $32,943.79 | |||
TSLA | Tesla | 2,156 | $6953 | $5942 | 306 | $ 82,362 | 89 | $32,943.80 | 306 | $ 82,362 | 89 | $32,943.80 | ||
AMD | Advanced Micro Devices | 2,157 | $6953 | $5942 | 307 | $ 82,363 | 90 | $32,943.81 | 307 | $ 82,363 | 90 | $32,943.81 |
Expected result shoud still look like your sample output but with other columns.
@icecold,
What does it mean 11/31 in column headers? Is it a date? There is no date 31th of November...
Is this what you like to see?
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("nZG9CoMwGEVfRULHUGJi/kZB6lJx0E0cxKZFCAZq6/P0WfpkjUkRnYRsN5fvcg6kaUBRXWoAQTH0TzOZ+8vm7wfDmCYunZikxCcqE2zT+NbaFQT9TyKBIWHIPQT3HcFQJuTMRcCihQ3IyzK3fW7MQ6vViR470S0h9gSxJ8iAxeJUV9fU9rWadLcqsWMltgVgD5A7gEABi0UpLTJbp7e5G3t1i9wvRpmah15NqyI/VuRboL+SaA+MAxZt+wM=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Customer Code" = _t, #"Customer Description" = _t, #"Budget Unit " = _t, #"Budget Revenue" = _t, #"Monthly billing amount" = _t, Column1 = _t, #"Billed Unit-10/31 " = _t, #"Billed Amount-10/31 " = _t, #"Unit Variance-10/31" = _t, #"Amount Variance-10/31" = _t, Column2 = _t, #"Billed Unit-11/31 " = _t, #"Billed Amount-11/31 " = _t, #"Unit Variance-11/31" = _t, #"Amount Variance-11/31" = _t]),
// Columns starting with "Column"
RemovedColumns = Table.SelectColumns(Source, List.Select(Table.ColumnNames(Source), each not Text.StartsWith(_, "Column"))),
// Unpivot columns with "-" and "/" in name
UnpivotedColumns = Table.Unpivot(RemovedColumns, List.Select(Table.ColumnNames(RemovedColumns), each List.ContainsAll(Text.ToList(_), {"-","/"})) , "Category", "Value"),
Split_Cat_to_Cat_Month = Table.SplitColumn(UnpivotedColumns, "Category", Splitter.SplitTextByEachDelimiter({"-"}, QuoteStyle.Csv, true), {"Category", "Month"}),
MonthFormat = Table.TransformColumns(Split_Cat_to_Cat_Month, {{"Month", each Date.ToText(Date.FromText("2024/" & Text.BeforeDelimiter(_, "/") & "/01", [Format="yyyy/MM/dd"]), "MMMM", "en-US"), type text}})
in
MonthFormat
Hello, @icecold demote header, transpose table and split column with "month - category"