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
Thanks so much for your responses, and apologies for the delay in getting the info back to you. When loading to Power BI this is how it looks.....
In regards to determining whether column1 is a Location category or sub-category the rules are if it immediately follows a null value or = 'total' then it is a category, then the subsequent ones are sub-categories under that particular category, until you hit another null value. I hope that makes sense. In regards to the Offence category, these are all text fields in column 2. Measures (Number and %) are in separate columns in the second row.
When the data changes they can sometimes add in additional categories/sub-categories and offences, or they add in an additional year which means extra columns etc.
Here is a link to the excel file which has the source data along with some queries that shows my [basic] data transfromations. If you look at table_4 you'll see that it has 3 levels of categories which are only discernible by indentations which don't carry through to the loaded table....
Am having some issues with your code which I'll keep playing around with...
thanks!