Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

AutoRemovedColumns1 is accessing data sources that have privacy levels which cannot be used together

Hi There,

 

I'm trying to reverse geolocation information based on Lat and Long through my google maps API and source of the information(lat & long) is located on a sharepoint folder.

 

My PowerBi pbix file has the Privacy setting adjusted. 
- Ignore the Privacy Levels ,etc 

It works perfectly locally (Power BI deskop) but the following errors occur on powerbi.com

 

This error occurs when I invoked a custom function within the same data source query,
"[Unable to combine data] Section1/Location/AutoRemovedColumns1 is accessing data sources that have privacy levels which cannot be used together. Please rebuild this data combination"

 

A different error occurs when trying to stage the source information and then using a secoud query to referrence the stage source with the added custom function.
https://www.excelguru.ca/blog/2015/03/11/power-query-errors-please-rebuild-this-data-combination/.
[Unable to combine data] Section1/Query1 (2)/AutoRemovedColumns1 references other queries or steps, so it may not directly access a data source. Please rebuild this data combination

 

Here is my code 

 

let
Source = SharePoint.Files("https://oursharepointfolder/", [ApiVersion = 15]),
#"Filtered Hidden Files1" = Table.SelectRows(Source, each [Attributes]?[Hidden]? <> true),
Navigation1 = Source{0}[Content],
#"Imported Excel" = Excel.Workbook(Navigation1),
#"Expanded Data" = Table.ExpandTableColumn(#"Imported Excel", "Data", {"Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7"}, {"Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7"}),
#"Promoted Headers" = Table.PromoteHeaders(#"Expanded Data", [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Sheet1", type text}, {"Site Name", type text}, {"Address", type text}, {"Co-Ordinates", type number}, {"Co-Ordinates_1", type number}, {"Supplier", type text}, {"Solution", type text}, {"Curcuit ID", type text}, {"Sheet1_2", type text}, {"Sheet", type text}, {"false", type logical}}),
#"Removed Top Rows" = Table.Skip(#"Changed Type",1),
#"Filtered Rows" = Table.SelectRows(#"Removed Top Rows", each ([#"Co-Ordinates"] <> null)),
#"Inserted Merged Column" = Table.AddColumn(#"Filtered Rows", "Location", each Text.Combine({Text.From([#"Co-Ordinates"], "en-US"), Text.From([#"Co-Ordinates_1"], "en-US")}, ","), type text),
#"Changed Type1" = Table.TransformColumnTypes(#"Inserted Merged Column",{{"Location", type text}}),
#"Invoked Custom Function" = Table.AddColumn(#"Changed Type1", "Query2 (2)", each #"Query2 (2)"([Location])),
#"Expanded Query2 (2)" = Table.ExpandTableColumn(#"Invoked Custom Function", "Query2 (2)", {"street_address", "route", "postal_code", "localitypolitical", "administrative_area_level_1political", "countrypolitical"}, {"street_address", "route", "postal_code", "localitypolitical", "administrative_area_level_1political", "countrypolitical"})
in
#"Expanded Query2 (2)"

 

###########################################################################################
Query 2 is my custom Fuction query

 

let

Findaddress = (addresslocation) =>


let
Source = Json.Document(Web.Contents("https://maps.googleapis.com",
[RelativePath="maps/api/geocode/json?latlng="&addresslocation&"&key=Keyhasbeeneditedout"])),
results = Source[results],
#"Converted to Table" = Table.FromList(results, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"formatted_address", "types"}, {"formatted_address", "types"}),
#"Extracted Values" = Table.TransformColumns(#"Expanded Column1", {"types", each Text.Combine(List.Transform(_, Text.From)), type text}),
#"Filtered Rows" = Table.SelectRows(#"Extracted Values", each ([types] <> "administrative_area_level_2political" and [types] <> "administrative_area_level_3political" and [types] <> "politicalsublocalitysublocality_level_1")),
#"Transposed Table" = Table.Transpose(#"Filtered Rows"),
#"Reversed Rows" = Table.ReverseRows(#"Transposed Table"),
#"Promoted Headers" = Table.PromoteHeaders(#"Reversed Rows", [PromoteAllScalars=true])

in
#"Promoted Headers"

in

Findaddress
########################################################################################

I'm not sure how else to structure the query so that it works in powerbi.com service. Hope there is a way?

 

 

Kind regards,

Rayno

Status: New
Comments
v-qiuyu-msft
Community Support

Hi @Anonymous ,

 

Does the issue occurs on Power BI service? 

 

I guess the issue is related to this: https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/9312540-make-functions-refreshable-when-the-data-source-is

 

Please backup the pbix file, then remove custom function, hardcode the addresslocation value in Query2, publish the report to Power BI service, check if the same issue occurs. 

 

Best Regards,
Qiuyun Yu 

Anonymous
Not applicable

Hi @v-qiuyu-msft 
Will be looking into the link you provided, Here is some more details.

A college has shared an excel document from their one drive for business and shared it with me. The link takes me to the sharePoint destination.

 

The excel document has been successfully imported with a working custom function to find the addresses from lat's and long's ( 
-22.338304000000001,30.042366999999999) | PowerBI Desktop

This all works perfectly on the PowerBI desktop but keeps failing in the PowerBI cloud service.


From my understanding both sources are external
1.) SharePoint (which is the onedrive saved location)
        Used the web connection to the excel document as per,

https://www.youtube.com/watch?v=t4TzHu8THoA 
        
2.) Google API call to retieve
        Referenced the proccess in the link, 
https://www.youtube.com/watch?v=87UsaOYD-ZM 
        but did make some changes to the query as below which fixed the error about query not being supported.

#############

Source = Json.Document(Web.Contents("https://maps.googleapis.com",
[RelativePath="maps/api/geocode/json",
Query=[latlng=addresslocation, key=Keyhasbeeneditedout]])),

#############

The following additional steps where test
1.) Non Staged Query 
Works perfectly on PowerBI desktop | Fails in the PowerBI Cloud Service

This is the error from the PowerBI Cloud Service:
##[Unable to combine data] Section1/Query1/AutoRemovedColumns1 is accessing data sources that have privacy levels which cannot be used together. Please rebuild this data combination##

2.) Staged Query as per https://www.excelguru.ca/blog/2015/03/11/power-query-errors-please-rebuild-this-data-combination/ 
Works perfectly on PowerBI desktop | Fails in the PowerBI Cloud Service
This is the error from the PowerBI Cloud Service:
##[Unable to combine data] Section1/Sheet1 (2)/AutoRemovedColumns1 references other queries or steps, so it may not directly access a data source. Please rebuild this data combination##

3.) Excel document was moved to a sharedfolder & later again to "c:\PorjectFolder\data.xlsx | Both results are as follows
Works perfectly on PowerBI desktop | Fails in the PowerBI Cloud Service
This is the error from the PowerBI Cloud Service: Note - A Personal gateway was used and this was a NON Staged data query.

-2147467259 Table: Sheet1.
Underlying error message:[Unable to combine data] Section1/Sheet1/AutoRemovedColumns1 is accessing data sources that have privacy levels which cannot be used together. Please rebuild this data combination.
DM_ErrorDetailNameCode_UnderlyingHResult:-2147467259


Conclusion:

It all works via PowerBI Deskop and fails on PowerBI cloud.

External sources                                                                 | Failed
Moved source to a local folder via the personal gateway | Failed

Its like its ignoring the Privacy levels in the pbix file.

Anonymous
Not applicable

Here is an article given to me by 
If I understand correctly the Power Query Firewall thats being mentioned in the article keeps the data partitions seperate and prevents data leaks.

Now in PowerBI desktop those errors that I mentioned do happen, 

Non Staged
"[Unable to combine data] Section1/Location/AutoRemovedColumns1 is accessing data sources that have privacy levels which cannot be used together. Please rebuild this data combination"

&

Staged
https://www.excelguru.ca/blog/2015/03/11/power-query-errors-please-rebuild-this-data-combination/.
[Unable to combine data] Section1/Query1 (2)/AutoRemovedColumns1 references other queries or steps, so it may not directly access a data source. Please rebuild this data combination

Once the Privacy levels on the Data sources are changed to Public those errors disappear in PowerBI Desktop (thats why I stated it works in PowerBI Desktop), however once its published to the PowerBi Cloud service those errors return. Due to the Sources being external web connections the Power BI Cloud service {under Data Source credentials} informs that we need to edit the credentials as per screenshot.

Notice that its doesn't ask anything about Privacy levels (assuming its looking at our Privacy levels set in the dataset)


2019-03-06 09_37_17-Power BI.png



2019-03-06 09_38_15-Power BI.png

This is the error after a manual refresh in the PowerBI Cloud Service

2019-03-06 09_43_38-Power BI.png

My opinion, the Privacy levels are not being referenced in the dataset and their is no option to tell the service what Privacy level to go on.
Also note, using the Personal gateway there is no option to tell the service which Privacy level to select so again we assume it'll referrence the Privacy levels in the dataset. It doesnt, the error also shows up using the personal gateway.

The game changes however if you add the sources to the Enterprise gateway data connections, there a person can select which Privacy Level to use but for some reason we cant add a online sharepoint(onedrive) connection. (please see if you are able)

For the ultimate test, I'll do the following

Move the excel document to a shared Folder and will now add the sources to the enterprise gateway + select the correct Privacy Level (assuming its not checking the Privacy Levels in the dataset) and test.

Kind Regards,

Rayno
 

Anonymous
Not applicable

Hi,

After moving the document again to the share folder and adding both sources to the enterprise gateway (with both advanced settings Privacy level set to Public) the PowerBI Cloud service successfully refreshed the data without any errors. 

There after I reproduced the error by changing one of the sources Privacy settings on the enterprise gate to organisation where the other remained Public, and there you have it the formidable error.

There you have it, the Privacy level setting in Power Desktop doesnt carry over to the Power BI service correctly with regards to sources being external. Nor is there an option under Data source credentials to set the Privacy level.

&
We can not add the cloud sharepoint folder connection to the enterprise gateway in order to set the Privacy level. I'm not sure why the enterprise gateway keeps failing to add the cloud sharepoint folder connect "Invalid credentials" 

Now,
I'm forced to move the excel document off the onedrive(sharePoint folder) to a share folder on the file server for the solution to be automated.

See there are a good couple of other similar issues on the form. 

Kind regards,
Rayno

 

raynok
Frequent Visitor

Just replying to this thread so that it can be answered and closed. Please also not that I reclaimed my original PowerBI community account so I'm back to using Raynok and no longer use rayno. 

 

Here is the answer to this particular issue. https://powerbi.microsoft.com/en-us/blog/privacy-levels-for-cloud-data-sources/