Forum Discussion
Dynamic data transformation with complex pivoted dataset
the code per the proportion part.
Only the second line is changed:
from -> colsNum=List.FirstN(Table.ColumnNames(Foglio2_Sheet),numCols),
to -> colsNum=List.RemoveRange(Table.ColumnNames(Foglio2_Sheet),1,numCols-1),
And the name NUMBER to PC in the third last line.
numCols=List.PositionOf(Record.FieldValues(Foglio2_Sheet{2}),"PROPORTION (%)"), colsNum=List.RemoveRange(Table.ColumnNames(Foglio2_Sheet),1,numCols-1), tabNum=Table.SelectColumns(Foglio2_Sheet,colsNum), #"Removed Top Rows" = Table.Skip(tabNum,3), #"Promoted Headers" = Table.PromoteHeaders(#"Removed Top Rows", [PromoteAllScalars=true]), tab = Table.AddColumn(#"Promoted Headers", "Offence", each let noNull=List.RemoveMatchingItems( Record.FieldValues(_),{null,""}) in if List.Count(noNull)=1 then noNull{0} else null), cols=Table.ColumnNames(tab), supLoc = List.Accumulate(tab[Location],{},(s,c)=> if c="" or List.Last(s)= "" or c="Total" then s&{c} else s&{List.Last(s)}), tab1= Table.FromColumns( Table.ToColumns(tab)&{supLoc},cols&{"supLocation"}), #"Filled Down" = Table.FillDown(tab1,{"Offence"}), #"Filtered Rows" = Table.SelectRows(#"Filled Down", each ([supLocation] <> null and [supLocation] <>"")), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Filtered Rows", {"Offence", "supLocation", "Location"}, "Year", "Value"), #"Sorted Rows" = Table.Sort(#"Unpivoted Other Columns",{{"Year", Order.Ascending},{"Offence", Order.Ascending},{"supLocation", Order.Ascending}}), #"Added Custom" = Table.AddColumn(#"Sorted Rows", "Measure", each "PC"), #"Reordered Columns" = Table.ReorderColumns(#"Added Custom",{"Year", "supLocation", "Location", "Offence", "Measure", "Value"}) in #"Reordered Columns"
the code that makes use of the complete tables and transformation criteria you have provided.
This is relative to the measure = number, for the measure = proportion if needed it is obtained with quick changes.
numCols=List.PositionOf(Record.FieldValues(Foglio2_Sheet{2}),"PROPORTION (%)"),
colsNum=List.FirstN(Table.ColumnNames(Foglio2_Sheet),numCols),
tabNum=Table.SelectColumns(Foglio2_Sheet,colsNum),
#"Removed Top Rows" = Table.Skip(tabNum,3),
#"Promoted Headers" = Table.PromoteHeaders(#"Removed Top Rows", [PromoteAllScalars=true]),
tab = Table.AddColumn(#"Promoted Headers", "Offence", each let noNull=List.RemoveMatchingItems( Record.FieldValues(_),{null,""}) in if List.Count(noNull)=1 then noNull{0} else null),
cols=Table.ColumnNames(tab),
supLoc = List.Accumulate(tab[Location],{},(s,c)=> if c="" or List.Last(s)= "" or c="Total" then s&{c} else s&{List.Last(s)}),
tab1= Table.FromColumns( Table.ToColumns(tab)&{supLoc},cols&{"supLocation"}),
#"Filled Down" = Table.FillDown(tab1,{"Offence"}),
#"Filtered Rows" = Table.SelectRows(#"Filled Down", each ([supLocation] <> null and [supLocation] <>"")),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Filtered Rows", {"Offence", "supLocation", "Location"}, "Year", "Value"),
#"Sorted Rows" = Table.Sort(#"Unpivoted Other Columns",{{"Year", Order.Ascending},{"Offence", Order.Ascending},{"supLocation", Order.Ascending}}),
#"Added Custom" = Table.AddColumn(#"Sorted Rows", "Measure", each "NUMBER"),
#"Reordered Columns" = Table.ReorderColumns(#"Added Custom",{"Year", "supLocation", "Location", "Offence", "Measure", "Value"})
in
#"Reordered Columns"
Hi M_Aird ,
about the other tableyou mentioned here:"This is another example of the variations of table layout I'm hoping this might work with....
is a totally different story.
Only to give an example, but there are many other differences:
as you can see and as far as I can interpret, the location and sub-location is not identified by the empty line but by the indentation.
So the logic for grouping should be adapted.
the code per the proportion part.
Only the second line is changed:
from -> colsNum=List.FirstN(Table.ColumnNames(Foglio2_Sheet),numCols),
to -> colsNum=List.RemoveRange(Table.ColumnNames(Foglio2_Sheet),1,numCols-1),
And the name NUMBER to PC in the third last line.
numCols=List.PositionOf(Record.FieldValues(Foglio2_Sheet{2}),"PROPORTION (%)"), colsNum=List.RemoveRange(Table.ColumnNames(Foglio2_Sheet),1,numCols-1), tabNum=Table.SelectColumns(Foglio2_Sheet,colsNum), #"Removed Top Rows" = Table.Skip(tabNum,3), #"Promoted Headers" = Table.PromoteHeaders(#"Removed Top Rows", [PromoteAllScalars=true]), tab = Table.AddColumn(#"Promoted Headers", "Offence", each let noNull=List.RemoveMatchingItems( Record.FieldValues(_),{null,""}) in if List.Count(noNull)=1 then noNull{0} else null), cols=Table.ColumnNames(tab), supLoc = List.Accumulate(tab[Location],{},(s,c)=> if c="" or List.Last(s)= "" or c="Total" then s&{c} else s&{List.Last(s)}), tab1= Table.FromColumns( Table.ToColumns(tab)&{supLoc},cols&{"supLocation"}), #"Filled Down" = Table.FillDown(tab1,{"Offence"}), #"Filtered Rows" = Table.SelectRows(#"Filled Down", each ([supLocation] <> null and [supLocation] <>"")), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Filtered Rows", {"Offence", "supLocation", "Location"}, "Year", "Value"), #"Sorted Rows" = Table.Sort(#"Unpivoted Other Columns",{{"Year", Order.Ascending},{"Offence", Order.Ascending},{"supLocation", Order.Ascending}}), #"Added Custom" = Table.AddColumn(#"Sorted Rows", "Measure", each "PC"), #"Reordered Columns" = Table.ReorderColumns(#"Added Custom",{"Year", "supLocation", "Location", "Offence", "Measure", "Value"}) in #"Reordered Columns"This is great - thanks so much!
