Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

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...
Need Help
Tips and Tricks
Tutorial Requests
  • dufoq3's avatar
    dufoq3
    2 years ago

    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"

     

Anonymous's avatar
Anonymous
Not applicable
2 years ago

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 CodeCustomer Description  Budget Unit Budget RevenueMonthly billing amount  Billed Unit-10/31  Billed Amount-10/31 Unit Variance-10/31Amount Variance-10/31  Billed Unit-11/31  Billed Amount-11/31 Unit Variance-11/31Amount Variance-11/31
MSFTMicrosoft 2,154 $6953 $5942  304 $ 82,360 87 $32,943.78  304 $ 82,360 87 $32,943.78
GOOGGoogle 2,155 $6953 $5942  305 $ 82,361 88 $32,943.79  305 $ 82,361 88 $32,943.79
TSLATesla 2,156 $6953 $5942  306 $ 82,362 89 $32,943.80  306 $ 82,362 89 $32,943.80
AMDAdvanced Micro Devices 2,157 $6953 $5942  307 $ 82,363 90 $32,943.81  307 $ 82,363 90 $32,943.81
dufoq3's avatar
dufoq3
Community Champion
2 years ago

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

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

    • dufoq3's avatar
      dufoq3
      Community Champion
      2 years ago

      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