Power Query Unpivot Columns Error

[Chtorr00]

I have 300k Rows of data in the Power Query for my data table, and I am trying to create 4 additional calculated columns, using a base year, and growth rates for the various categories in my data.

I merged my data with another query that contained the growth rates, and then was able to calculate my 4 additional columns in PQ. That part works like a charm.

The problem is: when I try to unpivot my original columns (2015) and the 4 calculated columns (2020, 2025, 2030, 2035), it seems to work superficially, but only 1k rows of data get loaded. When I click on Load More, I get this error:

[DataFormat.Error] Invalid cell value '#DIV/0!'.

Which is rather puzzling because in the calculations above there is no division (and in any case, the calculated columns had the full data prior the final unpivot step). All 5 pivoted rows are formatted to whole numbers, if that is relevant.

I am using Excel 2013. I am not performing the unpivoting using PQ and a table in excel because of the 1mil row limit, and I am not using calculated columns in Power Pivot because I need years as an attribute.

Any ideas what may be causing the problem? Here is the M code. #"Unpivoted Other Columns" is what triggers the error

let
Source = Table.NestedJoin(MergedTables,{"OCC_CODE"},CAGR,{"SOC"},"NewColumn",JoinKind.LeftOuter),
#"Expanded NewColumn" = Table.ExpandTableColumn(Source, "NewColumn", {"cagr"}, {"NewColumn.cagr"}),
#"Renamed Columns" = Table.RenameColumns(#"Expanded NewColumn",{{"NewColumn.cagr", "CAGR"}}),
#"Reordered Columns" = Table.ReorderColumns(#"Renamed Columns",{"GeoArea", "GeoId", "AreaNum", "StateName", "CityName", "ST", "OCC_CODE", "LocQ", "MedianWage", "CAGR", "2015"}),
#"Filtered Rows" = Table.SelectRows(#"Reordered Columns", each ([2015] <> "**")),
#"Changed Type5" = Table.TransformColumnTypes(#"Filtered Rows",{{"MedianWage", type text}}),
#"Replaced Value" = Table.ReplaceValue(#"Changed Type5","*","",Replacer.ReplaceText,{"MedianWage"}),
#"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","#","",Replacer.ReplaceText,{"MedianWage"}),
#"Changed Type6" = Table.TransformColumnTypes(#"Replaced Value1",{{"MedianWage", Int64.Type}}),
#"Added Custom" = Table.AddColumn(#"Changed Type6", "CAGRBase", each (1+[CAGR])),
#"Inserted Power" = Table.AddColumn(#"Added Custom", "Inserted Power", each Number.Power([CAGRBase], 5), type number),
#"Added Custom1" = Table.AddColumn(#"Inserted Power", "2020", each [2015]*[Inserted Power]),
#"Changed Type" = Table.TransformColumnTypes(#"Added Custom1",{{"2020", Int64.Type}}),
#"Inserted Power1" = Table.AddColumn(#"Changed Type", "Inserted Power.1", each Number.Power([CAGRBase], 10), type number),
#"Added Custom2" = Table.AddColumn(#"Inserted Power1", "2025", each [2015]*[Inserted Power.1]),
#"Changed Type1" = Table.TransformColumnTypes(#"Added Custom2",{{"2025", Int64.Type}}),
#"Inserted Power2" = Table.AddColumn(#"Changed Type1", "Inserted Power.2", each Number.Power([CAGRBase], 15), type number),
#"Added Custom3" = Table.AddColumn(#"Inserted Power2", "2030", each [2015]*[Inserted Power.2]),
#"Changed Type2" = Table.TransformColumnTypes(#"Added Custom3",{{"2030", Int64.Type}}),
#"Inserted Power3" = Table.AddColumn(#"Changed Type2", "Inserted Power.3", each Number.Power([CAGRBase], 20), type number),
#"Added Custom4" = Table.AddColumn(#"Inserted Power3", "Custom", each [2015]*[Inserted Power.3]),
#"Renamed Columns1" = Table.RenameColumns(#"Added Custom4",{{"Custom", "2035"}}),
#"Changed Type3" = Table.TransformColumnTypes(#"Renamed Columns1",{{"2035", Int64.Type}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type3",{"CAGR", "CAGRBase", "Inserted Power", "Inserted Power.1", "Inserted Power.2", "Inserted Power.3", "StateName", "CityName", "ST", "GeoArea", "GeoId"}),
#"Changed Type4" = Table.TransformColumnTypes(#"Removed Columns",{{"MedianWage", Int64.Type}, {"2015", Int64.Type}, {"2020", Int64.Type}, {"2025", Int64.Type}, {"2030", Int64.Type}, {"2035", Int64.Type}}),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type4", {"MedianWage", "LocQ", "OCC_CODE", "AreaNum"}, "Attribute", "Value")
in
#"Unpivoted Other Columns"


Thanks for any suggestions you might have,

Mark

 

[Chtorr00]

After some extensive digging. I managed to resolve this error on my one. It turns out that there was a div/0 error on one of the source spreadsheets. That fixed the Div/0 error, but then another Data.Format error cropped up. That was caused by 10 entries having a hash tag instead of a value (and being missed by a filter search due to spacing oddities.

Lesson Learned: Unpivoting is very sensitive to errors in your data. Clicking on the Keep Errors option and finding/resolving your error issues is a primary strategy for dealing with a post-unpivot error.

Mark