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"
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 | 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 |
Anonymous, Ok, but paste here also expected result based on new more accurate data. You have to also specify what does it mean 11/31 in column headers because there is no 31th of November
Expected result shoud still look like your sample output but with other columns.
Anonymous,
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