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"
waiting for your information, I made assumptions about the logic of the structure of the input table and, for the transformation, I used the following code,which you should hang at the end of the loaded table
....
tab = Table.AddColumn(yourTab, "Offence", each if List.Count(List.RemoveNulls( Record.FieldValues(_)))=1 then List.RemoveNulls( Record.FieldValues(_)){0} else null),
cols=Table.ColumnNames(tab),
supLoc = List.Accumulate(tab[Location],{},(s,c)=> if c=null or List.Last(s)= null 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)),
#"Reordered Columns" = Table.ReorderColumns(#"Filtered Rows",{"Offence", "supLocation", "Location", "2010", "2011", "2012", "2013"}),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Reordered Columns", {"Offence", "supLocation", "Location"}, "Attribute", "Value")
in
#"Unpivoted Other Columns"
here a screen shot of the result
This code is great thanks! The only thing is I had to delete the top row (which has the Measure field in it....it's almost like two tables side by side with cols 2 -10 being NUMBER and columns 11-19 being PROPORTION (%)) and promote the headers to get it to work, but then it's missing the measure and I can't work out how to include it....hopefully with the data source I sent it might make more sense? I did try the code with another table which didn't have that additional layer and it worked! so that's awesome.
Hi M_Aird
I tryed to send a private message tu you, but seems not possible for the moment 😕.
So here it is ..
I'm glad the code works for you. I will use the file you sent me to continue the development and add the management of the other aspects that I had left out pending your clarifications.
But I want to answer in the forum and for this you have to give me permission to use your data in the forum.
Bye
roccoThat's awesome thanks Rocco.
Yep that is fine - it is all publicly available data.
thanks again for your time!!
This is another example of the variations of table layout I'm hoping this might work with....
and I provided some clarifications around your initial questions but can't see them on here now? Are you still waiting on anything from me?
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"