Merge causing endless cycle of prompts for credentials.

[ThisIsFalse]

Hello I am new to this community and have not been using Power BI that long. I have run into a problem dealing with the data sorces. When I try to refresh the dataset the tables start asking to re-enter the credentials and it it will not stop prompting for them even if they are correct. I have looked at possible solutions and the one that seemed to apply was that I had "external queries" and was merging other queries into them. Changing the privicy settings seemed to resolve that issue. It seems, however, after this last update that the Queries broke and would no longer refresh from the gateway. The downloaded PBIX would not in the desktop either. I tried the second solution of making staging tables, but this has not solved the problem. I also disabled parrallel queries. Here is the code for the table in question. (I should note that I work for the most part with the power M side of things).

 Source = f_TagStage0,
    #"Merged Queries" = Table.NestedJoin(Source,{"DEPTDESC"},d_Department,{"Department"},"d_Department",JoinKind.LeftOuter),
    #"Expanded d_Department" = Table.ExpandTableColumn(#"Merged Queries", "d_Department", {"Id"}, {"d_Department.Id"}),
    #"Merged Queries1" = Table.NestedJoin(#"Expanded d_Department",{"DIV"},d_Location,{"DIV"},"d_Location",JoinKind.LeftOuter),
    #"Expanded d_Location" = Table.ExpandTableColumn(#"Merged Queries1", "d_Location", {"Id"}, {"d_Location.Id"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Expanded d_Location",null,"Misc",Replacer.ReplaceValue,{"TMTRL"}),
    #"Merged Queries2" = Table.NestedJoin(#"Replaced Value1",{"TMTRL"},d_Species,{"TMTRL"},"d_Species",JoinKind.LeftOuter),
    #"Expanded d_Species" = Table.ExpandTableColumn(#"Merged Queries2", "d_Species", {"Id"}, {"d_Species.Id"}),
    #"Replaced Value2" = Table.ReplaceValue(#"Expanded d_Species",null,"Unknown",Replacer.ReplaceValue,{"VND_NAME"}),
    #"Merged Queries3" = Table.NestedJoin(#"Replaced Value2",{"VND_NAME"},d_Vendor{"Vendor"},"d_Vendor",JoinKind.LeftOuter),
    #"Expanded d_Vendor" = Table.ExpandTableColumn(#"Merged Queries3", "d_Vendor", {"Id"}, {"d_Vendor.Id"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded d_Vendor", {"DIV", "DEPTDESC", "VND_NAME", "TMTRL"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"d_Department.Id", "DepartmentId"}, {"d_Location.Id", "LocationId"}, {"d_Species.Id", "SpeciesId"}, {"d_Vendor.Id", "VendorId"}, {"WID", "Width"}}),
    #"Reordered Columns" = Table.ReorderColumns(#"Renamed Columns",{"AccountId", "LocationId", "DepartmentId", "VendorId", "SpeciesId", "CUT_DT", "REC_DT", "PCS", "TAG", "ORD_CST", "LEN", "BFT_LENG", "UNIT_CST"}),
    #"Added Custom" = Table.AddColumn(#"Reordered Columns", "Length", each if ([LEN]/100) > 3 then Text.From(Number.Round(([LEN]/100), 2)) & " ft" else Text.From(Number.Round(([LEN]/100) * 12, 2)) & " in"),
    #"Changed Type" = Table.TransformColumnTypes(#"Added Custom",{{"Width", type number}})

   in
    #"Changed Type"

this seems to work until the second merge but gets stuck at the third.

any insight into this problem would be appriciated.

[ThisIsFalse]

I am replying to update this post with my current understanding of what I have learned. I tried the Buffer but it only seemed to hang. The prompts did go away but the query just seemed to hang there. I tried several things to see if I could make it work, the most effective seemed to be eliminating the "parallel queries" option. It is at this point I feel I should explain that I am querying a Pick database, so while it has SQL tables that can be queried it is not a natural thing for this datbase to do. When I could get it to work the data that came out seemed to have lost something, producing odd results. So I did not alter the dataset currently in the webservice, since for some reason it updated at least once per day (the report and the gateway confirmed this). However over the weekend all the scheduled refreshes went through. This leads me to believe that the ODBC connection must be timing out or busy and Power BI taking this as a refusal and then prompting me to enter credentials. In any event that is the only explanation I have for this behavior.  We are in the process of creating a data warehouse so I hope this will solve the issue. I realize that using the OLTP database is not the ideal solution for this kind of thing but this was out of my hands. Our comany is just starting with BI and the resident DBA is a bit old school. Thanks for all who took time to read and/or respond, I am going to mark this solved as I think that the issue has been (or will be) delt with.

PS. I maked the best answer that was not mine because they responded and it is probably a potential solution and it seems kind of gauche to make my response the solution. Anyway neither is what I would consider a sloution but there you have it.

PPS. I descoverd the underlying issue, and that was that the database ran out of licences when running the query.  The database has a licence pool, unfortunately because of the way power BI interacts with the tables in the database it was pulling more licences then were available, and that is why it had the credentials revoked.

[MarcelBeug · Accepted solution]

My first (and only) guess is that you might prevent reloading tables by using Table.Buffer, e.g. in each step before the merge steps, like:

#"Expanded d_Department" = Table.Buffer(Table.ExpandTableColumn(#"Merged Queries", "d_Department", {"Id"}, {"d_Department.Id"})),

(By the way, if you post code, then you can use the "Insert Code" icon at the left hand side of the smiley icon),

[ThisIsFalse]

Thanks for the tip,

I will try that and see what it gets me. I am also looking to see if I can shift some of the steps later down the line. I noticed that the code is adding tmrl several times to the tables. If I work that angle I may be able to get ride of some of the nested joins.