Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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.
Solved! Go to Solution.
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
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"
Thanks both, appreciate the support! It worked out nicely
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")))))
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
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"
Check out the July 2025 Power BI update to learn about new features.