cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
darentengmfs
Super User I
Super User I

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

16 REPLIES 16
darentengmfs
Super User I
Super User I

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
Super User I
Super User I

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

edhans
Super User III
Super User III

@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 III
Super User III

@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 IV
Super User IV

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

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




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

Proud to be a Super User!




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

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




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

Proud to be a Super User!




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

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




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

Proud to be a Super User!




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
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors