Add Index Function causes refresh to fail

[darentengmfs]

Hi,

I have a query that is created by 2 other queries. I added an index column using the built-in index column function and it loads just fine in Power BI and refreshes just fine. However, when I publish it to my app, the refresh failed in Power BI services. I am certain that the problem is with the index column but I can't figure out how to fix it.

Anyone faced this problem before? Is there another way to create an index column in Power Query (it has to be in PQ rather than DAX)?

Error Code in red:

Something went wrong
Unable to connect to the data source undefined.
Please try again later or contact support. If you contact support, please provide these details.
Underlying error code: -2147467259
Underlying error message: 5 arguments were passed to function which expects between 2 and 4.
DM_ErrorDetailNameCode_UnderlyingHResult: -2147467259
Microsoft.Data.Mashup.ValueError.Arguments: {Table.FromRecords({}), "Index", 1, 1, number}
Microsoft.Data.Mashup.ValueError.Reason: Expression.Error
Cluster URI: WABI-US-NORTH-CENTRAL-C-PRIMARY-redirect.analysis.windows.net
Activity ID: 83693713-ab81-4cad-8920-a062ea06a16e
Request ID: 964ab1de-87a4-9133-d5c8-b437ca197a9e
Time: 2020-08-17 17:56:16Z

Thanks!

Daren

[lbendlin · Accepted solution]

Try without the column type

#"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 1, 1)

[Anonymous]

I've had the same problem with SSAS tabular. While developing in Visual Studio, Power Query did not complain. After deploying to the server and trying to refresh the corresponding table I was facing the following error message:

Failed to save modifications to the server. Error returned: 'OLE DB or ODBC error: [Expression.Error] 5 arguments were passed to a function which expects between 2 and 4..
'.

Adding a Index to the table was implemented like this:
Table.AddIndexColumn(#"Replaced Value", "Index", 1, 1, Int64.Type)

After modifing to
Table.AddIndexColumn(#"Replaced Value", "ExtCpty Key", 1, 1)

the table refresh worked on the server as well.

[ImkeF]

Interesting @Anonymous , that definitely sounds like a bug that you might want to report.

[darentengmfs]

Hi @ImkeF, @Greg_Deckler, and @lbendlin 

I have used Table.Buffer in my sort function but it does not keep the sort when I apply the query. It reverts back when I go to table view. This is not a big issue here because the purpose of the sort in this query is to get the index correct.

On the other hand, removing the 5th parameter did help. My report is refreshing now. Some time in the future I have to get my data gateway updated so problems like this will not occur as often.

Thanks for both of your help!

[darentengmfs]

Hi @parry2k  @Greg_Deckler 

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"

[lbendlin · Accepted solution]

Try without the column type

#"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 1, 1)

[ImkeF · Accepted solution]

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.

[edhans]

@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.

[parry2k]

@darentengmfs can you share full M script?

[Greg_Deckler]

@darentengmfs - Can you post the M code from your query, at least the first few lines before and after where you add the Index column? Use Advanced Editor in Power Query editor. Looks like you are pass 5 parameters into a function that max'es out at 4

Otherwise, You could check the Issues forum here:

https://community.powerbi.com/t5/Issues/idb-p/Issues

And if it is not there, then you could post it.

If you have Pro account you could try to open a support ticket. If you have a Pro account it is free. Go to https://support.powerbi.com. Scroll down and click "CREATE SUPPORT TICKET".

[darentengmfs]

Hi @Greg_Deckler and @parry2k 

I have accidentally marked your post as solution.

Nonetheless, here are the lines before and after my Index. I have replied my full m script but it was taken down because someone reported it as spam so this would just be the last few lines.

#"Expanded inventItemGroup" = Table.ExpandTableColumn(#"Merged Queries1", "inventItemGroup", {"Item Group"}, {"Item Group"}),
#"Filtered Rows" = Table.SelectRows(#"Expanded inventItemGroup", each ([Item Group] <> "AAA" and [Item Group] <> "BBB") and ([Warehouse] = "100" or [Warehouse] = "200" or [Warehouse] = "300" or [Warehouse] = "400")),
#"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"

Also, I do have Pro license but I have never been able to submit a ticket. I had to go through my Power BI Administrator to submit one.

[parry2k]

@darentengmfs don't see any issue though. can you remove sort step and then add index. Can you test it, please?

[darentengmfs]

Hi @parry2k 

The sort has to be there before the index column. It is sorted descending on value because I had to get the rolling sum of value from highest to lowest.

[parry2k]

@darentengmfs hmmm, only for testing...just want to debug this.

[darentengmfs]

Hi @parry2k

Sorry it took so long. I removed the sort and the problem is still the same

Daren

[Greg_Deckler]

@darentengmfs - I don't see anything obviously wrong with the AddIndexColumn statement. However, try adding a Table.Buffer statement between your Sort and your adding of the index column. Maybe @ImkeF has some thoughts.

[darentengmfs]

Hi @Greg_Deckler 

I added Table.Buffer at the sort step and it doesn't refresh as well.

[Greg_Deckler]

@darentengmfs - Hmm, I would still recommend the Table.Buffer. It is often the case that the sorting doesn't "stick" and Table.Buffer tends to solve that. I learned that technique from @ImkeF and she may have some thoughts here as well, or possibly @edhans .

[ImkeF]

Absolutely @Greg_Deckler ,

thanks for reminding me.

@darentengmfs  you can check my article here where I've described this dangerous behaviour: https://community.powerbi.com/t5/Community-Blog/Bug-warning-for-Table-Sort-and-removing-duplicates-i...