Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
I am struggling with refreshing data coming off multiple web API’s which I combine using a parameter in the URL. The data refreshes fine in Power BI desktop, but I cannot get it to refresh within the service. The error message is:
[Unable to combine data] Section1/V7ClientsStaging2/Added Custom1 references other queries or steps, so it may not directly access a data source. Please rebuild this data combination. Table: V7Clients.
I have tried privacy settings, staging tables (as per https://www.excelguru.ca/blog/2015/03/11/power-query-errors-please-rebuild-this-data-combination/) and still cannot get it to work.
A recommendation off another blog was to add the function definition into the main query. I'm struggling with the syntax however to get this to work. Any assistance would be appreciated.
Main Query (the function is AssessmentsInfo, bolded below):
let
Source = {
ClientDetails[RootAreaGuid]
},
#"ListOfLists" = List.Combine(Source),
#"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "AreaGuidsForFunction"}}),
#"Expanded AreaGuidsForFunction" = Table.ExpandListColumn(#"Renamed Columns", "AreaGuidsForFunction"),
#"Added Custom1" = Table.AddColumn(#"Expanded AreaGuidsForFunction", "Custom", each AssessmentsInfo([AreaGuidsForFunction])),
#"Merged Queries" = Table.NestedJoin(#"Added Custom1",{"AreaGuidsForFunction"},ClientDetails,{"RootAreaGuid"},"ClientDetails",JoinKind.LeftOuter),
#"Expanded ClientDetails" = Table.ExpandTableColumn(#"Merged Queries", "ClientDetails", {"ClientName"}, {"ClientName"})
in
#"Expanded ClientDetails"
AssessmentsInfo function definition:
(AreaAPI as text) as list =>
let
Source = Json.Document(Web.Contents(“https://abc.xxx.com”,
[
RelativePath= “/api/v1/gateway/platform/”&AreaAPI&”/assessmentcriteria”
]
))
in
Source
AssessmentsInfo function definition within the Main Query (not working, gives a column of non invoked functions):
let
Source = {
ClientDetails[RootAreaGuid]
},
#"ListOfLists" = List.Combine(Source),
#"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "AreaGuidsForFunction"}}),
#"Expanded AreaGuidsForFunction" = Table.ExpandListColumn(#"Renamed Columns", "AreaGuidsForFunction"),
#"Added Custom1" = Table.AddColumn(#"Expanded AreaGuidsForFunction", "Custom",
each
(AreaAPI as text) as list =>
let
Source = Json.Document(Web.Contents(“https://abc.xxx.com”,
[
RelativePath= “/api/v1/gateway/platform/”&AreaAPI&”/assessmentcriteria”
]
))
in
Source
),
#"Merged Queries" = Table.NestedJoin(#"Added Custom1",{"AreaGuidsForFunction"},ClientDetails,{"RootAreaGuid"},"ClientDetails",JoinKind.LeftOuter),
#"Expanded ClientDetails" = Table.ExpandTableColumn(#"Merged Queries", "ClientDetails", {"ClientName"}, {"ClientName"})
in
#"Expanded ClientDetails"
Any assistance on the syntax of adding the function definition within the query would be much appreciated!
Solved! Go to Solution.
That would be done like so:
let AssessmentsInfoFunction = (AreaAPI as text) as list => let Source = Json.Document(Web.Contents(“https://abc.xxx.com”, [ RelativePath= “/api/v1/gateway/platform/”&AreaAPI&”/assessmentcriteria” ] )) in Source, Source = {ClientDetails[RootAreaGuid]}, #"ListOfLists" = List.Combine(Source), #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error), #"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "AreaGuidsForFunction"}}), #"Expanded AreaGuidsForFunction" = Table.ExpandListColumn(#"Renamed Columns", "AreaGuidsForFunction"), #"Added Custom1" = Table.AddColumn(#"Expanded AreaGuidsForFunction", "Custom", each AssessmentsInfoFunction([AreaGuidsForFunction])), #"Merged Queries" = Table.NestedJoin(#"Added Custom1",{"AreaGuidsForFunction"},ClientDetails,{"RootAreaGuid"},"ClientDetails",JoinKind.LeftOuter), #"Expanded ClientDetails" = Table.ExpandTableColumn(#"Merged Queries", "ClientDetails", {"ClientName"}, {"ClientName"}) in #"Expanded ClientDetails"
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
@ImkeF might be able to assist.
That would be done like so:
let AssessmentsInfoFunction = (AreaAPI as text) as list => let Source = Json.Document(Web.Contents(“https://abc.xxx.com”, [ RelativePath= “/api/v1/gateway/platform/”&AreaAPI&”/assessmentcriteria” ] )) in Source, Source = {ClientDetails[RootAreaGuid]}, #"ListOfLists" = List.Combine(Source), #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error), #"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "AreaGuidsForFunction"}}), #"Expanded AreaGuidsForFunction" = Table.ExpandListColumn(#"Renamed Columns", "AreaGuidsForFunction"), #"Added Custom1" = Table.AddColumn(#"Expanded AreaGuidsForFunction", "Custom", each AssessmentsInfoFunction([AreaGuidsForFunction])), #"Merged Queries" = Table.NestedJoin(#"Added Custom1",{"AreaGuidsForFunction"},ClientDetails,{"RootAreaGuid"},"ClientDetails",JoinKind.LeftOuter), #"Expanded ClientDetails" = Table.ExpandTableColumn(#"Merged Queries", "ClientDetails", {"ClientName"}, {"ClientName"}) in #"Expanded ClientDetails"
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
@Greg_Deckler Thanks for the referral.
@ImkeF Thanks so much for showing me how to define and and invoke a function within a query. This worked correctly.
I'm still however having the issue when uploading to Power BI service: [Unable to combine data] Section1/V7ClientsStaging1/AutoRemovedColumns1 references other queries or steps, so it may not directly access a data source. Please rebuild this data combination. Table: V7ClientsStaging1.
This suggestion to add the definition of the main function came from Chris Webb's blog.
At this point I'm at a complete loss on how to fix this error, I've tried privacy settings, staging tables in multiple forms, rewriting queries. Any other suggestions I could pursue would be most welcome.
Please check out this article:
http://blog.datainspirations.com/2018/02/17/dynamic-web-contents-and-power-bi-refresh-errors/
Don't know if it contains sth useful for your case, though.
Have you tried integrating the content of your "Source"-step 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
I had a look at the blog entry you suggested without any luck. Thanks for the input on this though.
When you say "Have you tried integrating the content of your "Source"-step as well?" Are you referring to the Source as below?
If so, I tried adding the query content into the step directly, with the source being a dataflows query. Still no change 😞
Hi There,
I'm also struggling with the same error in powerbi.com. 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.
This following error happens when I invoked a custom function with 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"
This following error happens when I first stage the source information and then creating a secoud query to referrence the stage source with the added customer function
[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
As you can see, stagging for not stagging the error persists in the cloud
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
The article I referenced in my previous post states that you have to use query parameters to make this successful:
so instead of this:
Source = Json.Document(Web.Contents("https://maps.googleapis.com", [RelativePath="maps/api/geocode/json?latlng="&addresslocation&"&key=Keyhasbeeneditedout"])),
you have to write it like so:
Source = Json.Document(Web.Contents("https://maps.googleapis.com", [RelativePath="maps/api/geocode/json",
Query=[latlng=addresslocation, key=Keyhasbeeneditedout]])),
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
Hi I am not able to change Schema name via parameter. Could you please look in to my original post and help me out?
Hi I am not able to change schema name vi Parameter. Can you help me on this? please see my originla post here. Thanks.
I have same problem with a public api in Power BI Service
Could you help me?
let Moeda = (CodMoeda as text) => let Source = Json.Document(Web.Contents("https://olinda.bcb.gov.br", [ RelativePath= "/olinda/servico/PTAX/versao/v1/odata/CotacaoMoedaPeriodo(moeda=@moeda,dataInicial=@dataInicial,dataFinalCotacao=@dataFinalCotacao)?@moeda='"&CodMoeda&"'&@dataInicial='01-01-2000'&@dataFinalCotacao='12-31-9999'&$top=10000&$filter=tipoBoletim%20eq%20'Fechamento'&$orderby=dataHoraCotacao%20desc&$format=json", Query=[select="cotacaoCompra,cotacaoVenda,dataHoraCotacao,tipoBoletim"]])), #"Convertido para Tabela" = Record.ToTable(Source), #"Linhas Filtradas" = Table.SelectRows(#"Convertido para Tabela", each ([Name] = "value")), #"Value Expandido" = Table.ExpandListColumn(#"Linhas Filtradas", "Value") in #"Value Expandido", Fonte = Json.Document(Web.Contents("https://olinda.bcb.gov.br/olinda/servico/PTAX/versao/v1/odata/Moedas?$top=100&$format=json&$select=simbolo,nomeFormatado,tipoMoeda")), #"Convertido para Tabela" = Record.ToTable(Fonte), #"Linhas Filtradas" = Table.SelectRows(#"Convertido para Tabela", each ([Name] = "value")), #"Outras Colunas Removidas" = Table.SelectColumns(#"Linhas Filtradas",{"Value"}), #"Value Expandido" = Table.ExpandListColumn(#"Outras Colunas Removidas", "Value"), #"Value Expandido1" = Table.ExpandRecordColumn(#"Value Expandido", "Value", {"nomeFormatado", "simbolo"}, {"nomeFormatado", "simbolo"}), #"Função Personalizada Invocada" = Table.AddColumn(#"Value Expandido1", "Consulta1 (2)", each Moeda([simbolo])), #"Consulta1 (2) Expandido" = Table.ExpandTableColumn(#"Função Personalizada Invocada", "Consulta1 (2)", {"Value"}, {"Value"}), #"Value Expandido2" = Table.ExpandRecordColumn(#"Consulta1 (2) Expandido", "Value", {"cotacaoCompra", "cotacaoVenda", "dataHoraCotacao", "tipoBoletim"}, {"cotacaoCompra", "cotacaoVenda", "dataHoraCotacao", "tipoBoletim"}), #"Tipo Alterado" = Table.TransformColumnTypes(#"Value Expandido2",{{"nomeFormatado", type text}, {"simbolo", type text}, {"cotacaoCompra", type number}, {"cotacaoVenda", type number}, {"dataHoraCotacao", type datetime}, {"tipoBoletim", type text}}), #"Data Extraída" = Table.TransformColumns(#"Tipo Alterado",{{"dataHoraCotacao", DateTime.Date, type date}}) in #"Data Extraída"
Example API return USD
https://olinda.bcb.gov.br/olinda/servico/PTAX/versao/v1/odata/CotacaoMoedaPeriodo(moeda=@moeda,dataInicial=@dataInicial,dataFinalCotacao=@dataFinalCotacao)?@moeda='USD'&@dataInicial='01-01-2000'&@dataFinalCotacao='12-31-9999'&$top=10000&$filter=tipoBoletim%20eq%20'Fechamento'&$orderby=dataHoraCotacao%20desc&$format=json&$select=cotacaoCompra,cotacaoVenda,dataHoraCotacao,tipoBoletim
@ImkeF wrote:The article I referenced in my previous post states that you have to use query parameters to make this successful:
so instead of this:
Source = Json.Document(Web.Contents("https://maps.googleapis.com", [RelativePath="maps/api/geocode/json?latlng="&addresslocation&"&key=Keyhasbeeneditedout"])),you have to write it like so:
Source = Json.Document(Web.Contents("https://maps.googleapis.com", [RelativePath="maps/api/geocode/json",
Query=[latlng=addresslocation, key=Keyhasbeeneditedout]])),
You have to adjust the first parameters of your Web.Contents-functions with longer URLs that actually return something: http://blog.datainspirations.com/2018/02/17/dynamic-web-contents-and-power-bi-refresh-errors/.
And don't worry, that will be overwritten by the relative path and query parameters, once the query runs.
So sth like this for the first Web.Contents (don't know what shall go into the 2nd):
let Moeda = (CodMoeda as text) => let Source = Json.Document(Web.Contents("https://olinda.bcb.gov.br/olinda/servico/PTAX/versao/v1/odata/CotacaoMoedaPeriodo(moeda=@moeda,dataInicial=@dataInicial,dataFinalCotacao=@dataFinalCotacao)?@moeda='USD'&@dataInicial='01-01-2000'&@dataFinalCotacao='12-31-9999'&$top=10000&$filter=tipoBoletim%20eq%20'Fechamento'&$orderby=dataHoraCotacao%20desc&$format=json&$select=cotacaoCompra,cotacaoVenda,dataHoraCotacao,tipoBoletim", [ RelativePath= "/olinda/servico/PTAX/versao/v1/odata/CotacaoMoedaPeriodo(moeda=@mhttps://olinda.bcb.gov.br/olinda/servico/PTAX/versao/v1/odata/CotacaoMoedaPeriodo(moeda=@moeda,dataInicial=@dataInicial,dataFinalCotacao=@dataFinalCotacao)?@moeda='USD'&@dataInicial='01-01-2000'&@dataFinalCotacao='12-31-9999'&$top=10000&$filter=tipoBoletim%20eq%20'Fechamento'&$orderby=dataHoraCotacao%20desc&$format=json&$select=cotacaoCompra,cotacaoVenda,dataHoraCotacao,tipoBoletimoeda,dataInicial=@dataInicial,dataFinalCotacao=@dataFinalCotacao)?@moeda='"&CodMoeda&"'&@dataInicial='01-01-2000'&@dataFinalCotacao='12-31-9999'&$top=10000&$filter=tipoBoletim%20eq%20'Fechamento'&$orderby=dataHoraCotacao%20desc&$format=json", Query=[select="cotacaoCompra,cotacaoVenda,dataHoraCotacao,tipoBoletim"]])), #"Convertido para Tabela" = Record.ToTable(Source), #"Linhas Filtradas" = Table.SelectRows(#"Convertido para Tabela", each ([Name] = "value")), #"Value Expandido" = Table.ExpandListColumn(#"Linhas Filtradas", "Value") in #"Value Expandido", Fonte = Json.Document(Web.Contents("https://olinda.bcb.gov.br/olinda/servico/PTAX/versao/v1/odata/Moedas?$top=100&$format=json&$select=simbolo,nomeFormatado,tipoMoeda")), #"Convertido para Tabela" = Record.ToTable(Fonte), #"Linhas Filtradas" = Table.SelectRows(#"Convertido para Tabela", each ([Name] = "value")), #"Outras Colunas Removidas" = Table.SelectColumns(#"Linhas Filtradas",{"Value"}), #"Value Expandido" = Table.ExpandListColumn(#"Outras Colunas Removidas", "Value"), #"Value Expandido1" = Table.ExpandRecordColumn(#"Value Expandido", "Value", {"nomeFormatado", "simbolo"}, {"nomeFormatado", "simbolo"}), #"Função Personalizada Invocada" = Table.AddColumn(#"Value Expandido1", "Consulta1 (2)", each Moeda([simbolo])), #"Consulta1 (2) Expandido" = Table.ExpandTableColumn(#"Função Personalizada Invocada", "Consulta1 (2)", {"Value"}, {"Value"}), #"Value Expandido2" = Table.ExpandRecordColumn(#"Consulta1 (2) Expandido", "Value", {"cotacaoCompra", "cotacaoVenda", "dataHoraCotacao", "tipoBoletim"}, {"cotacaoCompra", "cotacaoVenda", "dataHoraCotacao", "tipoBoletim"}), #"Tipo Alterado" = Table.TransformColumnTypes(#"Value Expandido2",{{"nomeFormatado", type text}, {"simbolo", type text}, {"cotacaoCompra", type number}, {"cotacaoVenda", type number}, {"dataHoraCotacao", type datetime}, {"tipoBoletim", type text}}), #"Data Extraída" = Table.TransformColumns(#"Tipo Alterado",{{"dataHoraCotacao", DateTime.Date, type date}}) in #"Data Extraída"
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
Hi There,
I've reclaimed my orginal PowerBI community account Raynok and no longer use rayno.
Just to keep you all in the know I believe this article also played a part as I could get it to work on PowerBI desktop but not in the cloud. https://community.powerbi.com/t5/Issues/AutoRemovedColumns1-is-accessing-data-sources-that-have-priv...
Hi ImkeF,
Thank you for this, I made the changes as advised and it works for PowerBI deskop but still errors in the PowerBI cloud service.
I've read alot of forms and articles, (saw you also commented in a few I read - nice one).
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
I used the web connection to the excel document as per,
https://www.youtube.com/watch?v=t4TzHu8THoA
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)
2.) Google API call to retieve
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 sharedfolder + 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 is 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
Hi @Rayno & @Anonymous
I suggest you read this article: https://social.technet.microsoft.com/Forums/en-US/ca434e2d-88fe-4962-b46a-a1db51e8bd89/feedback-wanted-behind-the-scenes-of-the-data-privacy-firewall?forum=powerquery
give good feedback and try to motivate Ehren to continue his series, where he will hopefully come up with the solution-part as well 😉
Maybe you get some ideas how to tweak your code further, but considering the complexity of the matter, I don't see how I can help you any further in the forum here unfortunately.
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
Thanks @ImkeF for the link to Ehren's series and your willingness to give feedback to the community.
This issue has been extremely frustrating and I've spent countless hours trying to figure it out without success.
Thanks @ImkeF , that link definitely helped.
I always say " We always tell computers what to do" so if it works on PowerBI Desktop(As example we told it to ignore Privacy levels) then there must be someone( a developer ) that didnt tell the PowerBI cloud service to listen to our Privacy settings within our PowerBI Pbix file with regards to external data sources.
So how we overide it, by telling the PowerBI cloud service what to do, we manually Add the sources to the enterprise gateway,
The enterprise gateway allows one to choose under advance setting the Privacy level which in turns corrects the Privacy level.
So now it works and I've confirmed data changes.
I believe this needs to be corrected by the PowerBI team.
My idea, they need to add the ability to either take in the Privacy level configured within the pbix file or they need to provide the ability to set the Privacy level under the Data Set setting -> Data Source Credentials.
Otherwise for now, add the source connections to the enterprise gateway and set the Privacy Levels.
Thank you again for the article and for your guidance.
Kind regards,
Rayno
PS, Dont stage the source information when trying the now mentioned workaround.
It didnt work for me, so I rolled back to the orginal which was to,
Invoke the function within the same Dataquery
Kind regards,
Rayno
Check out the September 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
109 | |
94 | |
83 | |
32 | |
27 |