Forum Discussion
Adding custom date column and group Columns by date
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 th...
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 Anonymous, you can do it:
- Create blank query
- Open it in Advanced Editor
- Delete whole code and paste there the one I created
- Replace highlighted part with your query name reference (i.e. MyData or #"My Data" if you have some spaces or special characters in name) where you imported your excel data. Don't forget comma at the end as you can see on picture below.
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 Google 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