How to proper split a single column with multiple budget levels in PQ

[SuneP]

Hello all

Working on data, not proper formatted as Excel table. The budget levels are combined in a single column, and through PQ, I want to split and pivot the budget levels. See here further below. How would you guys proceed?  Thanks in advance.

[SuneP]

Thanks both, appreciate the support! It worked out nicely

[wdx223_Daniel]

import your data into PQ with headers, such as "Column1","Month 1","Month 2"....

NewStep = Table.FromPartitions("Week",Table.ToRows(Table.Group(Source,"Column1",{"n",each Table.FromPartitions("Weekday",Table.ToRows(Table.Group(Table.Skip(_),"Column1",{"n",each Table.UnpivotOtherColumns(Table.RemoveColumns(Table.Skip(_),"Total Months"),{"Column1"},"Month","Value")},0,(x,y)=>Byte.From(List.Contains({"Monday","Tuesday","Wednesday","Thursday","Friday","Saturday","Sunday"},y)))))},0,(x,y)=>Byte.From(Text.StartsWith(y,"Week")))))

[AlexisOlson · Accepted solution]

• Create columns for Week, Day, and Fruit.

if Text.StartsWith([Column1], "Week") then [Column1] else null

if Text.EndsWith([Column1], "day") then [Column1] else null

if [Week] = null and [Day] = null then [Column1] else null

• Fill down Week and Day
• Filter out null rows in Fruit.
• Remove Column1
• Unpivot Month 1 and Month 2
• Extract number from Month and Week columns

Here's a full sample query you can paste into the Advanced Editor of a new blank query:

let
  Source = Table.FromRows(
    {
      {"Week 1", "60", "60", "120"}, 
      {"Monday", "30", "30", "60"}, 
      {"Apples", "10", "10", "20"}, 
      {"Bananas", "20", "20", "40"}, 
      {"Tuesday", "30", "30", "60"}, 
      {"Apples", "20", "10", "30"}, 
      {"Bananas", "10", "20", "30"}, 
      {"Week 2", "25", "90", "115"}, 
      {"Monday", "9", "30", "39"}, 
      {"Apples", "4", "10", "17"}, 
      {"Bananas", "5", "20", "25"}, 
      {"Tuesday", "6", "30", "36"}, 
      {"Apples", "2", "10", "12"}, 
      {"Bananas", "4", "20", "24"}, 
      {"Wednesday", "10", "30", "40"}, 
      {"Apples", "8", "10", "18"}, 
      {"Bananas", "2", "20", "22"}
    }, 
    type table [Column1 = text, Month 1 = text, Month 2 = text, Total Months = text]
  ),
    #"Removed Columns" = Table.RemoveColumns(Source,{"Total Months"}),
    #"Added Week" = Table.AddColumn(#"Removed Columns", "Week", each if Text.StartsWith([Column1], "Week") then [Column1] else null, type text),
    #"Added Day" = Table.AddColumn(#"Added Week", "Day", each if Text.EndsWith([Column1], "day") then [Column1] else null, type text),
    #"Added Fruit" = Table.AddColumn(#"Added Day", "Fruit", each if [Week] = null and [Day] = null then [Column1] else null, type text),
    #"Filled Down" = Table.FillDown(#"Added Fruit",{"Week", "Day"}),
    #"Removed Columns1" = Table.RemoveColumns(#"Filled Down",{"Column1"}),
    #"Filtered Rows" = Table.SelectRows(#"Removed Columns1", each ([Fruit] <> null)),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Filtered Rows", {"Week", "Day", "Fruit"}, "Month", "Amount"),
    #"Reordered Columns" = Table.ReorderColumns(#"Unpivoted Columns",{"Month", "Week", "Day", "Fruit", "Amount"}),
    #"Extracted Text After Delimiter" = Table.TransformColumns(#"Reordered Columns", {{"Month", each Number.From(Text.AfterDelimiter(_, " ")), Int64.Type}, {"Week", each Number.From(Text.AfterDelimiter(_, " ")), Int64.Type}})
in
    #"Extracted Text After Delimiter"