Power Query: transform a table with sub-items.

[T.I.]

Good day,
I need to transform a table with sub-items via Power Query.
Desired output in attached file.
P.S. I've already done it via formulas, but I need solution via Power Query.

[oeldere]

It looks the format of a pivot table.

Maybe it is better to change the original data.

[T.I.]

Maybe it is better to change the original data.

Unfortunately it is not possible.

[oeldere]

Then make the result by pivot table on the original data.

I suppose there is no need to use Power Query for this.

[T.I.]

Original data has a lot more records, so for flexibility I prefer to use Power Query (I believe it's possible).

[alansidman]

Here is a power query solution

let 
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], 
    #"Added Custom" = Table.AddColumn(Source, "CODE.1", each if Text.Contains([Code],[Code]) then [Code] else null), 
    #"Replaced Errors" = Table.ReplaceErrorValues(#"Added Custom", {{"CODE.1", null}}), 
    #"Filled Down" = Table.FillDown(#"Replaced Errors",{"CODE.1"}), 
    #"Added Custom1" = Table.AddColumn(#"Filled Down", "Category", each if Text.Contains([Name],"Category") then [Name] else null), 
    #"Filled Down1" = Table.FillDown(#"Added Custom1",{"Category"}), 
    #"Filtered Rows" = Table.SelectRows(#"Filled Down1", each ([Revenue] <> null)), 
    #"Renamed Columns" = Table.RenameColumns(#"Filtered Rows",{{"CODE.1", "Letter Code"}}), 
    #"Reordered Columns" = Table.ReorderColumns(#"Renamed Columns",{"Code", "Letter Code", "Category", "Name", "Revenue"}) 
in 
    #"Reordered Columns"  

[T.I.]

Great! Works as expected! Thank you Alan.

[Czeslaw]

Another option

Please Login or Register  to view this content.

[Bo_Ry]

Please try

let 
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], 
    Group = Table.Group(Source, "Name", {{"T", each (_)}},0,(b,e)=> Number.From(Text.StartsWith(e,"Cate")) ), 
    AddCol = Table.AddColumn(Group, "TA", each Table.AddColumn(Table.AddColumn(Table.RemoveFirstN([T]),"Letter Code",(x)=> [T][Code]{0}),"Catagory",(x)=> [T][Name]{0}) ), 
    Combine = Table.Combine(AddCol[TA]), 
    Reorder = Table.ReorderColumns(Combine,{"Code", "Letter Code", "Catagory", "Name", "Revenue"}) 
in 
    Reorder  

[T.I.]

Czeslaw Thank you, all works!

Bo_Ry Thank you, but somehow I have a problems with Query (pls. refer to a screenshot, and as I suppose this is due to my old Excel 2013)
When I press "Go to Error" it leads me to AddCol step. Update: Works on Excel 2016. Looks like a compatibility issue.
screen.png

P.S. Also, I want to thank you for your formula options. Better then mine