Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.