Adding custom date column and group Columns by date

[icecold]

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?

[dufoq3 · Accepted 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"

[icecold]

This is very promising. Can you update the sample source to point to an excel workbook instead of JSON doc? 

[dufoq3]

Hi @icecold, you can do it:

1. Create blank query
2. Open it in Advanced Editor
3. Delete whole code and paste there the one I created
4. 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.

[icecold]

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

[dufoq3]

@icecold, 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

[icecold]

Expected result shoud still look like your sample output but with other columns.

[dufoq3]

@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

[AlienSx]

Hello, @icecold demote header, transpose table and split column with "month - category"