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.
Hi. I have a complex case and I appreciate your help to transform my data.
I have this raw data
I would like to tranform my data to this table. I tried to search for solutions to make my in betweenrow data into separate columns. Thank you very much for your help.
Solved! Go to Solution.
Hi @third_hicana, here you are (but you provided wrong data in expected result for ID P002 - Project B)
Result
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCjAwMFTSUQooys9KTS5RcASynRMLUiuAtKGBAZAEoVidaBAdYmBgqmdkamYJZPtD1ACRsSlIGZCAqgIpzC9JzIGbgKIAaJ8Rkn1OSPZBkCHCJKB9hnqmZhamCPuMoEYhHIVkHUISbEgsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, #"Project Name" = _t, Budget = _t, Jan = _t, Feb = _t, March = _t]),
ReplacedValue = Table.ReplaceValue(Source,"",null,Replacer.ReplaceValue,{"ID", "Project Name"}),
Ad_Project = Table.AddColumn(ReplacedValue, "Project", each try if Text.StartsWith([Project Name], "Project", Comparer.OrdinalIgnoreCase) then [Project Name] else null otherwise null, type text),
FilledDown = Table.FillDown(Ad_Project,{"ID", "Project Name", "Project"}),
UnpivotedOtherColumns = Table.UnpivotOtherColumns(FilledDown, {"ID", "Project Name", "Budget", "Project"}, "Month", "Value"),
ChangedType = Table.TransformColumnTypes(UnpivotedOtherColumns,{{"Value", type number}}),
PivotedColumn = Table.Pivot(ChangedType, List.Distinct(ChangedType[Budget]), "Budget", "Value", List.Sum),
GroupedRows = Table.Group(PivotedColumn, {"ID", "Project", "Month"}, {{"All", each Table.FillDown(_, List.Distinct(Source[Budget])), type table}}),
CombinedAll = Table.Combine(GroupedRows[All]),
FilteredRows = Table.SelectRows(CombinedAll, each not Text.StartsWith([Project Name], "Project", Comparer.OrdinalIgnoreCase)),
Ad_SortHelper = Table.AddColumn(FilteredRows, "SortHelper", each Date.FromText("2024-" & Text.Start([Month], 3) & "-01", [Format="yyyy-MMM-dd", Culture="en-US"]), type date),
SortedRows = Table.Sort(Ad_SortHelper,{{"ID", Order.Ascending}, {"SortHelper", Order.Ascending}}),
RemovedColumns = Table.RemoveColumns(SortedRows,{"SortHelper"})
in
RemovedColumns
Hi @third_hicana, here you are (but you provided wrong data in expected result for ID P002 - Project B)
Result
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCjAwMFTSUQooys9KTS5RcASynRMLUiuAtKGBAZAEoVidaBAdYmBgqmdkamYJZPtD1ACRsSlIGZCAqgIpzC9JzIGbgKIAaJ8Rkn1OSPZBkCHCJKB9hnqmZhamCPuMoEYhHIVkHUISbEgsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, #"Project Name" = _t, Budget = _t, Jan = _t, Feb = _t, March = _t]),
ReplacedValue = Table.ReplaceValue(Source,"",null,Replacer.ReplaceValue,{"ID", "Project Name"}),
Ad_Project = Table.AddColumn(ReplacedValue, "Project", each try if Text.StartsWith([Project Name], "Project", Comparer.OrdinalIgnoreCase) then [Project Name] else null otherwise null, type text),
FilledDown = Table.FillDown(Ad_Project,{"ID", "Project Name", "Project"}),
UnpivotedOtherColumns = Table.UnpivotOtherColumns(FilledDown, {"ID", "Project Name", "Budget", "Project"}, "Month", "Value"),
ChangedType = Table.TransformColumnTypes(UnpivotedOtherColumns,{{"Value", type number}}),
PivotedColumn = Table.Pivot(ChangedType, List.Distinct(ChangedType[Budget]), "Budget", "Value", List.Sum),
GroupedRows = Table.Group(PivotedColumn, {"ID", "Project", "Month"}, {{"All", each Table.FillDown(_, List.Distinct(Source[Budget])), type table}}),
CombinedAll = Table.Combine(GroupedRows[All]),
FilteredRows = Table.SelectRows(CombinedAll, each not Text.StartsWith([Project Name], "Project", Comparer.OrdinalIgnoreCase)),
Ad_SortHelper = Table.AddColumn(FilteredRows, "SortHelper", each Date.FromText("2024-" & Text.Start([Month], 3) & "-01", [Format="yyyy-MMM-dd", Culture="en-US"]), type date),
SortedRows = Table.Sort(Ad_SortHelper,{{"ID", Order.Ascending}, {"SortHelper", Order.Ascending}}),
RemovedColumns = Table.RemoveColumns(SortedRows,{"SortHelper"})
in
RemovedColumns
Hi @dufoq3
Yes. Sorry. I copied the wrong values for Project B but you got what I wish to do. Thank you very much for your help
Third
Check out the July 2025 Power BI update to learn about new features.