Forum Discussion
Add Index Function causes refresh to fail
Try without the column type
#"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 1, 1)
Hi darentengmfs ,
As lbendlin said: Removing this newly added optional 5th parameter should solve the issue.
Alternatively you have to update the gateway in the service to the newest version. That should fix the problem as well.
Here is my full m script for this query
let
Source = Table.NestedJoin(inventSum,{"id"},activeCostVersion,{"id"},"activeCostVersion",JoinKind.Inner),
#"Expanded activeCostVersion" = Table.ExpandTableColumn(Source, "activeCostVersion", {"Cost"}, {"activeCostVersion.Cost"}),
#"Added Custom" = Table.AddColumn(#"Expanded activeCostVersion", "Inv Value", each [On Hand]*[activeCostVersion.Cost]),
#"Changed Type" = Table.TransformColumnTypes(#"Added Custom",{{"Inv Value", type number}}),
#"Added Custom1" = Table.AddColumn(#"Changed Type", "Item Cost Value", each [activeCostVersion.Cost]/[On Hand]),
#"Changed Type1" = Table.TransformColumnTypes(#"Added Custom1",{{"Item Cost Value", type number}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type1",{{"activeCostVersion.Cost", "Cost"}, {"Item Cost Value", "Inverse Item Value"}, {"Inv Value", "Inventory Value"}, {"Warehouse", "Warehouse"}}),
#"Uppercased Text" = Table.TransformColumns(#"Renamed Columns",{{"Item Number", Text.Upper, type text}, {"InventSum.InventDimID", Text.Upper, type text}, {"InventDim.InventDimID", Text.Upper, type text}}),
#"Renamed Columns1" = Table.RenameColumns(#"Uppercased Text",{{"id", "uniqueid"}}),
#"Merged Queries" = Table.NestedJoin(#"Renamed Columns1", {"Item Number"}, inventItemGroupItem, {"Item Number"}, "inventItemGroupItem", JoinKind.LeftOuter),
#"Expanded inventItemGroupItem" = Table.ExpandTableColumn(#"Merged Queries", "inventItemGroupItem", {"Item Group ID"}, {"Item Group ID"}),
#"Merged Queries1" = Table.NestedJoin(#"Expanded inventItemGroupItem", {"Item Group ID"}, inventItemGroup, {"Item Group ID"}, "inventItemGroup", JoinKind.LeftOuter),
#"Expanded inventItemGroup" = Table.ExpandTableColumn(#"Merged Queries1", "inventItemGroup", {"Item Group"}, {"Item Group"}),
#"Filtered Rows" = Table.SelectRows(#"Expanded inventItemGroup", each ([Item Group] <> "XXX" and [Item Group] <> "YYY") and ([Warehouse] = "AAA" or [Warehouse] = "BBB" or [Warehouse] = "CCC" or [Warehouse] = "DDD")),
#"Filtered Rows1" = Table.SelectRows(#"Filtered Rows", each [On Hand] >= 0),
#"Sorted Rows" = Table.Sort(#"Filtered Rows1",{{"Inventory Value", Order.Descending}}),
#"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 1, 1, Int64.Type)
in
#"Added Index"
Try without the column type
#"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 1, 1)
Hi darentengmfs ,
As lbendlin said: Removing this newly added optional 5th parameter should solve the issue.
Alternatively you have to update the gateway in the service to the newest version. That should fix the problem as well.
darentengmfs - if you do remove the 5th option, vs updating the gateway, I would recommend one of the last steps in your query is to convert that [Index] type to integer. By default it is a decimal number field. New versions of Power BI Desktop automatically add that 5th step, which I suspect is a model optimization. Integers store and perform better than decimal numbers.
