Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
SuneP
New Member

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

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.

 

PQ budget .png

 

 

1 ACCEPTED SOLUTION
AlexisOlson
Super User
Super User

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

 

AlexisOlson_0-1710171755120.png

 

View solution in original post

3 REPLIES 3
SuneP
New Member

Thanks both, appreciate the support! It worked out nicely

wdx223_Daniel
Super User
Super User

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

wdx223_Daniel_0-1710231522385.png

 

AlexisOlson
Super User
Super User

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

 

AlexisOlson_0-1710171755120.png

 

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.