Transfer row data into a separate column

[third_hicana]

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.

[dufoq3 · Accepted 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

[third_hicana]

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

[dufoq3]

You're welcome.