Forum Discussion
Add Index Function causes refresh to fail
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
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.
18 Replies
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.
Interesting Anonymous , that definitely sounds like a bug that you might want to report.
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".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.
darentengmfs don't see any issue though. can you remove sort step and then add index. Can you test it, please?
darentengmfs can you share full M script?
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.
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!
