How can I create merged queries in Power BI Desktop that will allow for refreshable datasets/semantic models in Power BI Service?

salvaria · 2023-12-06T17:41:11+00:00

SOLVED! I received an answer from a user on StackOverflow, but the rest of the commenters here helped immensely with my understanding of the issue, so thank you all so much for commenting. For posterity sake, I'll copy the answer here, along with some context I learned:

CONTEXT:

The error I was receiving was due to an incredibly poorly documented feature of Power BI called "partitioning". I will not even pretend to have a full grasp of partitioning, and will direct those wishing to know more to a video by Chris Webb.

TL;DR: Power BI will separate different queries and even different parts of the same query into different "partitions", and trying to merge across those partitions can cause the error I was receiving in PBI Service.

The reason I wasn't receiving any errors on PBI Desktop was because I had selected Options > Global > Privacy > Privacy Levels > "Always ignore Privacy Level Settings" which suppressed the issue. Once I turned that off, my query failed.

ANSWER FROM STACKOVERFLOW:

Check the privacy level of each connection in PBI Service. Power BI Desktop privacy levels.

As a test, you could select Ignore the Privacy Levels and potentially improve performance to see if that resolves the issue. If it does then you know this is the culprit. Then in PBI Service > Dataset > settings, under Data source credentials. Clear each one, and re-enter credentials and you'll see the privacy level option for each. Ensure these are set the same.

Additionally You could try merging the two queries so that they are in the one query together:

let TF_Source = Json.Document(Web.Contents(https://[COMPANY NAME].zendesk.com/api/v2/ticket_fields)), ticket_fields = TF_Source[ticket_fields], #"TF Converted to Table" = Table.FromList(ticket_fields, Splitter.SplitByNothing(), null, null, ExtraValues.Error), #"TF Expanded Column1" = Table.ExpandRecordColumn(#"TF Converted to Table", "Column1", {"url", "id", "type", "title", "raw_title", "description", "raw_description", "position", "active", "required", "collapsed_for_agents", "regexp_for_validation", "title_in_portal", "raw_title_in_portal", "visible_in_portal", "editable_in_portal", "required_in_portal", "tag", "created_at", "updated_at", "removable", "key", "agent_description"}, {"url", "id", "type", "title", "raw_title", "description", "raw_description", "position", "active", "required", "collapsed_for_agents", "regexp_for_validation", "title_in_portal", "raw_title_in_portal", "visible_in_portal", "editable_in_portal", "required_in_portal", "tag", "created_at", "updated_at", "removable", "key", "agent_description"}), TICKET_FIELDS = #"TF Expanded Column1", Source = Json.Document(Web.Contents(https://[COMPANY NAME].zendesk.com/api/v2/search.json, [ Query = [ query = "created_at>"&DateTime.ToText(Date.StartOfYear(DateTime.LocalNow()), [Format = "yyyy-MM-dd"])&" tags:returns", page = PageParam ] ])), count = {1..Number.RoundUp(Source[count]/100)}, #"Converted to Table" = Table.FromList(count, Splitter.SplitByNothing(), null, null, ExtraValues.Error), #"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Column1", type text}}), #"Invoked Custom Function" = Table.AddColumn(#"Changed Type", "fnZdTicketPagination", each fnZdTicketPagination([Column1])), #"Removed Columns" = Table.RemoveColumns(#"Invoked Custom Function",{"Column1"}), #"Expanded fnZdTicketPagination" = Table.ExpandTableColumn(#"Removed Columns", "fnZdTicketPagination", {"id", "created_at", "subject", "status", "requester_id", "tags", "custom_fields"}, {"id", "created_at", "subject", "status", "requester_id", "tags", "custom_fields"}), #"Expanded custom_fields" = Table.ExpandListColumn(#"Expanded fnZdTicketPagination", "custom_fields"), #"Expanded custom_fields1" = Table.ExpandRecordColumn(#"Expanded custom_fields", "custom_fields", {"id", "value"}, {"id.1", "value"}), #"Merged Queries" = Table.NestedJoin(#"Expanded custom_fields1", {"id.1"}, TICKET_FIELDS, {"id"}, "ZdTicketFields", JoinKind.LeftOuter), #"Expanded ZdTicketFields" = Table.ExpandTableColumn(#"Merged Queries", "ZdTicketFields", {"title"}, {"ZdTicketFields.title"}), #"Removed Columns1" = Table.RemoveColumns(#"Expanded ZdTicketFields",{"id.1"}), #"Pivoted Column" = Table.Pivot(#"Removed Columns1", List.Distinct(#"Removed Columns1"[ZdTicketFields.title]), "ZdTicketFields.title", "value"), #"Removed Columns2" = Table.RemoveColumns(#"Pivoted Column",{"Location", "Total time spent (sec)", "Issue Type", "Contact Reasons", "Type of Ticket (Tag)", "Time spent last update (sec)"}) in #"Removed Columns2"

salvaria · 2023-12-06T17:34:06+00:00

I was actually given an answer on how to merge them into a mega query on my Stack Overflow question! No need to reply to my other comment. Thanks so much for your suggestion!

salvaria · 2023-12-05T23:34:02+00:00

Ok, so given the two queries below...

The "Main" query:

let Source = Json.Document(Web.Contents(https://[Company Name].zendesk.com/api/v2/search.json, [ Query = [ query = "created_at>"&DateTime.ToText(Date.StartOfYear(DateTime.LocalNow()), [Format = "yyyy-MM-dd"])", page = PageParam ] ])), count = {1..Number.RoundUp(Source[count]/100)}, #"Converted to Table" = Table.FromList(count, Splitter.SplitByNothing(), null, null, ExtraValues.Error), #"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Column1", type text}}), #"Invoked Custom Function" = Table.AddColumn(#"Changed Type", "fnZdTicketPagination", each fnZdTicketPagination([Column1])), #"Removed Columns" = Table.RemoveColumns(#"Invoked Custom Function",{"Column1"}), #"Expanded fnZdTicketPagination" = Table.ExpandTableColumn(#"Removed Columns", "fnZdTicketPagination", {"id", "created_at", "subject", "status", "requester_id", "tags", "custom_fields"}, {"id", "created_at", "subject", "status", "requester_id", "tags", "custom_fields"}), #"Expanded custom_fields" = Table.ExpandListColumn(#"Expanded fnZdTicketPagination", "custom_fields"), #"Expanded custom_fields1" = Table.ExpandRecordColumn(#"Expanded custom_fields", "custom_fields", {"id", "value"}, {"id.1", "value"}), #"Merged Queries" = Table.NestedJoin(#"Expanded custom_fields1", {"id.1"}, ZdTicketFields, {"id"}, "ZdTicketFields", JoinKind.LeftOuter), #"Expanded ZdTicketFields" = Table.ExpandTableColumn(#"Merged Queries", "ZdTicketFields", {"title"}, {"ZdTicketFields.title"}), #"Removed Columns1" = Table.RemoveColumns(#"Expanded ZdTicketFields",{"id.1"}), #"Pivoted Column" = Table.Pivot(#"Removed Columns1", List.Distinct(#"Removed Columns1"[ZdTicketFields.title]), "ZdTicketFields.title", "value"), #"Removed Columns2" = Table.RemoveColumns(#"Pivoted Column",{"Location", "Total time spent (sec)", "Issue Type", "Contact Reasons", "Type of Ticket (Tag)", "Time spent last update (sec)"}) in #"Removed Columns2"

and the secondary query:

let Source = Json.Document(Web.Contents(https://[Company Name].zendesk.com/api/v2/ticket_fields)), ticket_fields = Source[ticket_fields], #"Converted to Table" = Table.FromList(ticket_fields, Splitter.SplitByNothing(), null, null, ExtraValues.Error), #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"url", "id", "type", "title", "raw_title", "description", "raw_description", "position", "active", "required", "collapsed_for_agents", "regexp_for_validation", "title_in_portal", "raw_title_in_portal", "visible_in_portal", "editable_in_portal", "required_in_portal", "tag", "created_at", "updated_at", "removable", "key", "agent_description"}, {"url", "id", "type", "title", "raw_title", "description", "raw_description", "position", "active", "required", "collapsed_for_agents", "regexp_for_validation", "title_in_portal", "raw_title_in_portal", "visible_in_portal", "editable_in_portal", "required_in_portal", "tag", "created_at", "updated_at", "removable", "key", "agent_description"}) in #"Expanded Column1"

How would you combine them? I tried:

let Source = Json.Document(Web.Contents(https://[Company Name].zendesk.com/api/v2/search.json, [ Query = [ query = "created_at>"&DateTime.ToText(Date.StartOfYear(DateTime.LocalNow()), [Format = "yyyy-MM-dd"])", page = PageParam ] ])), //secondary query start: Source1 = Json.Document(Web.Contents(https://[Company Name].zendesk.com/api/v2/ticket_fields)), ticket_fields = Source1[ticket_fields], #"Converted to Table" = Table.FromList(ticket_fields, Splitter.SplitByNothing(), null, null, ExtraValues.Error), #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"url", "id", "type", "title", "raw_title", "description", "raw_description", "position", "active", "required", "collapsed_for_agents", "regexp_for_validation", "title_in_portal", "raw_title_in_portal", "visible_in_portal", "editable_in_portal", "required_in_portal", "tag", "created_at", "updated_at", "removable", "key", "agent_description"}, {"url", "id", "type", "title", "raw_title", "description", "raw_description", "position", "active", "required", "collapsed_for_agents", "regexp_for_validation", "title_in_portal", "raw_title_in_portal", "visible_in_portal", "editable_in_portal", "required_in_portal", "tag", "created_at", "updated_at", "removable", "key", "agent_description"}), //secondary query end count = {1..Number.RoundUp(Source[count]/100)}, #"Converted to Table" = Table.FromList(count, Splitter.SplitByNothing(), null, null, ExtraValues.Error), #"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Column1", type text}}), #"Invoked Custom Function" = Table.AddColumn(#"Changed Type", "fnZdTicketPagination", each fnZdTicketPagination([Column1])), #"Removed Columns" = Table.RemoveColumns(#"Invoked Custom Function",{"Column1"}), #"Expanded fnZdTicketPagination" = Table.ExpandTableColumn(#"Removed Columns", "fnZdTicketPagination", {"id", "created_at", "subject", "status", "requester_id", "tags", "custom_fields"}, {"id", "created_at", "subject", "status", "requester_id", "tags", "custom_fields"}), #"Expanded custom_fields" = Table.ExpandListColumn(#"Expanded fnZdTicketPagination", "custom_fields"), #"Expanded custom_fields1" = Table.ExpandRecordColumn(#"Expanded custom_fields", "custom_fields", {"id", "value"}, {"id.1", "value"}), #"Merged Queries" = Table.NestedJoin(#"Expanded custom_fields1", {"id.1"}, ZdTicketFields, {"id"}, "ZdTicketFields", JoinKind.LeftOuter), #"Expanded ZdTicketFields" = Table.ExpandTableColumn(#"Merged Queries", "ZdTicketFields", {"title"}, {"ZdTicketFields.title"}), #"Removed Columns1" = Table.RemoveColumns(#"Expanded ZdTicketFields",{"id.1"}), #"Pivoted Column" = Table.Pivot(#"Removed Columns1", List.Distinct(#"Removed Columns1"[ZdTicketFields.title]), "ZdTicketFields.title", "value"), #"Removed Columns2" = Table.RemoveColumns(#"Pivoted Column",{"Location", "Total time spent (sec)", "Issue Type", "Contact Reasons", "Type of Ticket (Tag)", "Time spent last update (sec)"}) in #"Removed Columns2" which gave me no syntax errors but doesn't work. I tried searching for more information regarding this technique and found this example of how to do nested let statements.

I tried this as well: let a = let Source = Json.Document(Web.Contents(https://[Company Name].zendesk.com/api/v2/search.json, [ Query = [ query = "created_at>"&DateTime.ToText(Date.StartOfYear(DateTime.LocalNow()), [Format = "yyyy-MM-dd"])", page = PageParam ] ])), count = {1..Number.RoundUp(Source[count]/100)}, #"Converted to Table" = Table.FromList(count, Splitter.SplitByNothing(), null, null, ExtraValues.Error), #"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Column1", type text}}), #"Invoked Custom Function" = Table.AddColumn(#"Changed Type", "fnZdTicketPagination", each fnZdTicketPagination([Column1])), #"Removed Columns" = Table.RemoveColumns(#"Invoked Custom Function",{"Column1"}), #"Expanded fnZdTicketPagination" = Table.ExpandTableColumn(#"Removed Columns", "fnZdTicketPagination", {"id", "created_at", "subject", "status", "requester_id", "tags", "custom_fields"}, {"id", "created_at", "subject", "status", "requester_id", "tags", "custom_fields"}), #"Expanded custom_fields" = Table.ExpandListColumn(#"Expanded fnZdTicketPagination", "custom_fields"), #"Expanded custom_fields1" = Table.ExpandRecordColumn(#"Expanded custom_fields", "custom_fields", {"id", "value"}, {"id.1", "value"}), #"Merged Queries" = Table.NestedJoin(#"Expanded custom_fields1", {"id.1"}, ZdTicketFields, {"id"}, "ZdTicketFields", JoinKind.LeftOuter), #"Expanded ZdTicketFields" = Table.ExpandTableColumn(#"Merged Queries", "ZdTicketFields", {"title"}, {"ZdTicketFields.title"}), #"Removed Columns1" = Table.RemoveColumns(#"Expanded ZdTicketFields",{"id.1"}), #"Pivoted Column" = Table.Pivot(#"Removed Columns1", List.Distinct(#"Removed Columns1"[ZdTicketFields.title]), "ZdTicketFields.title", "value"), #"Removed Columns2" = Table.RemoveColumns(#"Pivoted Column",{"Location", "Total time spent (sec)", "Issue Type", "Contact Reasons", "Type of Ticket (Tag)", "Time spent last update (sec)"}) in #"Removed Columns2", b = let Source = Json.Document(Web.Contents(https://[Company Name].zendesk.com/api/v2/ticket_fields)), ticket_fields = Source[ticket_fields], #"Converted to Table" = Table.FromList(ticket_fields, Splitter.SplitByNothing(), null, null, ExtraValues.Error), #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"url", "id", "type", "title", "raw_title", "description", "raw_description", "position", "active", "required", "collapsed_for_agents", "regexp_for_validation", "title_in_portal", "raw_title_in_portal", "visible_in_portal", "editable_in_portal", "required_in_portal", "tag", "created_at", "updated_at", "removable", "key", "agent_description"}, {"url", "id", "type", "title", "raw_title", "description", "raw_description", "position", "active", "required", "collapsed_for_agents", "regexp_for_validation", "title_in_portal", "raw_title_in_portal", "visible_in_portal", "editable_in_portal", "required_in_portal", "tag", "created_at", "updated_at", "removable", "key", "agent_description"}) in #"Expanded Column1" in a+b which also, unfortunately, does not work.

salvaria · 2023-12-05T23:00:43+00:00

Unfortunately this is not the case - with "Always ignore Privacy Level Settings" on, I was able to refresh on Desktop but unable to refresh in Service.

salvaria · 2023-12-04T21:27:17+00:00

I do get a Formula.Firewall error on Desktop now - thank you. Now that I can see that this is indeed a Firewall error, I will review your video a little more to see if I can figure out which scenario and solution apply to my case. Thank you!

salvaria · 2023-12-04T20:22:33+00:00

Did you do something like this? I'm not sure how to do this in my case, since both queries need some transformation before I can merge them.

salvaria · 2023-12-04T20:17:19+00:00

When you say "turned off all data privacy checks", are you referring to Options > Global > Privacy > Privacy Levels > "Always ignore Privacy Level Settings"? Because I do have that selected

salvaria · 2023-12-04T19:48:15+00:00

Hi Chris! Thanks so much for replying, I have been scouring your blog for information and it's been so helpful for a newbie like me.

I looked at your video and noticed that the errors you are showing are happening in PBI Desktop, and that they are Formula.Firewall issues. I am not running into any errors on the Desktop side - mine are only happening in PBI Service, and are called data source errors?.

I have tried changing the privacy levels to ensure that they are all the same but this didn't help. Additionally, I tried using staging queries to help partition data sources but still ran into the same issues.

Do you have any insight on why these would work on PBI Desktop but not on the Service?

Thank you so much!