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.
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.
Last edited by T.I.; 07-03-2022 at 10:45 AM.
It looks the format of a pivot table.
Maybe it is better to change the original data.
Notice my main language is not English.
I appreciate it, if you reply on my solution.
If you are satisfied with the solution, please mark the question solved.
You can add reputation by clicking on the star * add reputation.
Then make the result by pivot table on the original data.
I suppose there is no need to use Power Query for this.
Original data has a lot more records, so for flexibility I prefer to use Power Query (I believe it's possible).
Here is a power query solution
PHP Code:
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"
Alan עַם יִשְׂרָאֵל חַי
Change an Ugly Report with Power Query
Database Normalization
Complete Guide to Power Query
Man's Mind Stretched to New Dimensions Never Returns to Its Original Form
Great! Works as expected! Thank you Alan.
Another optionPlease Login or Register to view this content.
Please try
PHP Code:
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
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
Last edited by T.I.; 07-04-2022 at 08:19 AM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks