Forum Discussion
Solved
Power Query help
Hi, Does anyone know how i can convert this using power query or from excel. The header is on the 1st column, it organize in a weekly fashion. The spreadsheet was not designed by me. But i n...
Good, so you add the category-field to the unpivot others step:
// Table1 let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], #"Filtered Rows2" = Table.SelectRows(Source, each ([Column1] <> null)), #"Added Custom" = Table.AddColumn(#"Filtered Rows2", "Week", each if Text.StartsWith([Column1], "Week ") then [Column1] else null), #"Filled Down" = Table.FillDown(#"Added Custom",{"Week"}), #"Promoted Headers" = Table.PromoteHeaders(#"Filled Down", [PromoteAllScalars=true]), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Promoted Headers", {"Week 1", "Week 1_1", "category"}, "Attribute", "Value"), #"Renamed Columns" = Table.RenameColumns(#"Unpivoted Other Columns",{{"Attribute", "Date"}}), #"Filtered Rows" = Table.SelectRows(#"Renamed Columns", each not Text.StartsWith([Week 1], "Week ")), #"Filtered Rows1" = Table.SelectRows(#"Filtered Rows", each ([Value] <> "")), #"Renamed Columns1" = Table.RenameColumns(#"Filtered Rows1",{{"Week 1", "Row"}, {"Week 1_1", "Week"}}), #"Changed Type1" = Table.TransformColumnTypes(#"Renamed Columns1",{{"Value", type number}, {"category", type text}}) in #"Changed Type1"
= Table.Combine(Table.Group(Source,"Column1",{"n",each let a=Table.PromoteHeaders(Table.SelectRows(_,each [Column1]<>null)),b=Table.UnpivotOtherColumns(a,List.RemoveRange(Table.ColumnNames(a),1,7),"Date","x") in Table.RenameColumns(Table.Pivot(b,List.Distinct(Table.ToColumns(b){0}),Table.ColumnNames(b){0},"x"),{{"dates","category"}},1)},0,(x,y)=>Byte.From(Text.StartsWith(y??"","Week ")))[n])