cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
darentengmfs
Post Prodigy
Post Prodigy

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

2 ACCEPTED SOLUTIONS

Try without the column type

 

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

View solution in original post

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.

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

View solution in original post

18 REPLIES 18
Anonymous
Not applicable

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.

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

darentengmfs
Post Prodigy
Post Prodigy

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
Post Prodigy
Post Prodigy

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"

Try without the column type

 

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

View solution in original post

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.

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

View solution in original post

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



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
parry2k
Super User
Super User

@darentengmfs can you share full M script?






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





Greg_Deckler
Super User
Super User

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


@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop! Microsoft Hates Greg's Quick Measures
YouTube Channel! Microsoft Hates Greg
Check out my latest book!

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?






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





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.

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






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





Hi @parry2k

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

Daren

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


@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop! Microsoft Hates Greg's Quick Measures
YouTube Channel! Microsoft Hates Greg
Check out my latest book!

Hi @Greg_Deckler 

 

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

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


@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop! Microsoft Hates Greg's Quick Measures
YouTube Channel! Microsoft Hates Greg
Check out my latest book!

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

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.