Refresh error when using NestedJoin

[primolee]

Hello everyone,

I have encountered an error refreshing in BI Service when I use NestedJoin function.  Here is the error message:

Section1/Query1/AutoRemovedColumns1 references other queries or steps, so it may not directly access a data source. Please rebuild this data combination."}}],"exceptionCulprit":1}}} Table: Query1.

I have used this method but still doesn't work.

https://www.excelguru.ca/blog/2015/03/11/power-query-errors-please-rebuild-this-data-combination/ 

let
    //Access Data source
    #"Site List" = SharePoint.Files(foldersPath, [ApiVersion = 15]),
    #"Filtered Rows" = Table.SelectRows(#"Site List", each Text.Contains([Folder Path], 

// Many steps, ignore

    #"Filtered Rows2" = Table.SelectRows(#"Uppercase Key", each true),

    //This is the error
    #"Merged Queries" = Table.NestedJoin(#"Filtered Rows2", {"Key"}, #"Consolidated Cue", {"Key"}, "Consolidated Cue", JoinKind.LeftOuter),
    #"Expanded Consolidated Cue" = Table.ExpandTableColumn(#"Merged Queries", "Consolidated Cue", {"Buying Type"}, {"Cue Buying Type"}),
    #"Added Conditional Column" = Table.AddColumn(#"Expanded Consolidated Cue", "Clicks/Views/Engagements", each if [Cue Buying Type] = "CPE" then [Engagements] else if [Cue Buying Type] = "LINE CPC" then [Link Clicks] else if [Cue Buying Type] = "CPV" then [Video Views] else [Clicks]),
    #"Changed Type2" = Table.TransformColumnTypes(#"Added Conditional Column",{{"Clicks/Views/Engagements", Int64.Type}})
in
    #"Changed Type2"

This query will work if I remove #"Merged Queries" and the steps afterwards, but it won't with #"Merged Queries".

I have also read something about Gateway, but all my data sources are excels in Sharepoint.

Does anyone know how to fix this?  or is there a way around it?

Thank you so much.

Best regards,

David

[edhans]

I've had issues with merges of sharepoint data when they were different sharepoint sites. Try this:

• In Power BI Desktop, clear your login credientials and re-enter them. Make sure you set all privacy settings to Organizational, or maybe none. I use Org.
• Publish your report.
• In the service, log in to the sharepoint services in the Scheduled Refresh section, log in to those and again, make sure the privacy settings match those you set above for each site.

Sometimes merging sharepoint resources from different sharepoint sites is more trouble than it is worth.

[primolee]

Hello @edhans ,

I just tried it, but it does not work.

All my data sources are in the same Sharepoint site.  Moreover, what I am merging is another processed query table in the same BI, and that query table's data source is from the same Sharepoint site as well.

The error message is the same.  Just like what all the other threads say, it is very irritating...

This merge is necessary to me, I have no other ways around it... >_<

Funny thing I noticed about the error message.  Although it looks the same, but there is a little difference.

Original:

Section1/Query1/AutoRemovedColumns1 references other queries or steps, so it may not directly access a data source. Please rebuild this data combination.

With the method you provided:

Section1/Query1/Changed Type 2 references other queries or steps, so it may not directly access a data source. Please rebuild this data combination.

In the original error, AutoRemovedColumns1 step is not in any of my queries.  However in the new error, Changed Type 2 is the last step of Query1.  Therefore, your method must have done something.

[primolee · Accepted solution]

Hello everyone,

Well, very idiotic, but actually the solution in the thread does work.

https://www.excelguru.ca/blog/2015/03/11/power-query-errors-please-rebuild-this-data-combination/  

I only referenced one query then start merging in my first trial, and it did not work in the first place.

This time I reference the final results of my 2 queries, then merge using these 2 new queries.  It now works in BI Service.

This is really a walk-around by making the outside sources into local sources by referencing.