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

• 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"