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!

Reply
Anonymous
Not applicable

Works on Desktop Fail on Service [Unable to combine data] Section1/

Hello, 

Not sure why PWBI Desktop refreshes and PWBI Service fails. 

  • Underlying error code-2147467259
  • Underlying error message - "[Unable to combine data] Section1/FACT Intervention Experiments Allocation STG/Source references other queries or steps, so it may not directly access a data source. Please rebuild this data combination."
  • Microsoft.Data.Mashup.MashupSecurityException.Reason - PrivacyError

 

 

 

// server
"localhost" meta [IsParameterQuery=true, Type="Text", IsParameterQueryRequired=true]

// FACT Intervention STG
let
    Source = Table.Buffer(MySQL.Database(server, "analytics", [ReturnSingleDatabase=true, Query="SELECT * FROM medicodb.intervention_flow", CommandTimeout=#duration(0, 2, 0, 0)]))
in
    Source

// FACT Intervention
let
    Source = Table.Buffer(#"FACT Intervention STG"),
    #"Filtered Rows" = Table.SelectRows(Source, each ([analytics_metadata] <> null)),
    #"Parsed JSON" = Table.TransformColumns(#"Filtered Rows",{{"analytics_metadata", Json.Document}}),
    #"Expanded analytics_metadata" = Table.ExpandRecordColumn(#"Parsed JSON", "analytics_metadata", {"decision", "recurring", "projectCode", "target_days", "planned_allocated", "experiment_start_date", "intervention_success_period", "goal"}, {"decision", "recurring", "projectCode", "target_days", "planned_allocated", "experiment_start_date", "intervention_success_period", "goal"}),
    #"Expanded analytics_metadata.decision" = Table.ExpandRecordColumn(#"Expanded analytics_metadata", "decision", {"decision_date", "decision_status", "decision_comment"}, {"decision_date", "decision_status", "decision_comment"}),
    #"Expanded analytics_metadata.goal" = Table.ExpandRecordColumn(#"Expanded analytics_metadata.decision", "goal", {"adherence", "retention", "has_profile", "has_pharmacy_requests"}, {"adherence", "retention", "has_profile", "has_pharmacy_requests"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded analytics_metadata.goal",{"intervention_name", "active", "created", "payload", "type", "handler", "intervention_success_period"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"id", "Intervention ID"}, {"decision_comment", "Decision Comment"}, {"decision_date", "Decision Date"}, {"decision_status", "Decision Status"}, {"experiment_start_date", "Experiment Start Date"}, {"planned_allocated", "Planned Allocated"}, {"projectCode", "Category"}, {"recurring", "Recurring"}, {"target_days", "Target Days"}})
in
    #"Renamed Columns"

// DIM Intevention STG
let
    Source = Table.Buffer(MySQL.Database(server, "analytics", [ReturnSingleDatabase=true, Query="SELECT *,CASE #(lf)WHEN payload LIKE '%ios%' THEN 'iOS'#(lf)WHEN payload LIKE '%android%' THEN 'android'#(lf)ELSE 'both'#(lf)END AS OS  FROM medicodb.intervention_flow", CommandTimeout=#duration(0, 2, 0, 0)]))
in
    Source

// DIM Intevention
let
    Source = Table.Buffer(#"DIM Intevention STG"),
    #"Filtered Rows1" = Table.SelectRows(Source, each ([analytics_metadata] <> null)),
    #"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows1",{"id", "intervention_name", "active", "created","OS"}),
    #"Merged Queries" = Table.NestedJoin(#"Removed Other Columns", {"id"}, #"DIM Intervention Channel", {"id"}, "DIM Intervention Channel", JoinKind.LeftOuter),
    #"Expanded DIM Intervention Channel" = Table.ExpandTableColumn(#"Merged Queries", "DIM Intervention Channel", {"Count"}, {"Count"}),
    #"Renamed Columns" = Table.RenameColumns(#"Expanded DIM Intervention Channel",{{"Count", "Channel"}}),
    #"Filtered Rows" = Table.SelectRows(#"Renamed Columns", each true),
    #"Duplicated Column" = Table.DuplicateColumn(#"Filtered Rows", "id", "id - Copy"),
    #"Renamed Columns1" = Table.RenameColumns(#"Duplicated Column",{{"id - Copy", "id_text"}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns1",{{"id_text", type text}}),
    #"Renamed Columns2" = Table.RenameColumns(#"Changed Type",{{"id", "Intervention ID"}, {"intervention_name", "Intervention Name"}}),
    #"Merged Queries1" = Table.NestedJoin(#"Renamed Columns2", {"Intervention ID"}, #"DIM Intervention Goal", {"id"}, "DIM Intervention Goal", JoinKind.LeftOuter),
    #"Expanded DIM Intervention Goal" = Table.ExpandTableColumn(#"Merged Queries1", "DIM Intervention Goal", {"Goal"}, {"Goal"})
in
    #"Expanded DIM Intervention Goal"

// DIM Intervention Channel STG
let
    Source = MySQL.Database(server, "analytics", [ReturnSingleDatabase=true, Query="SELECT * FROM medicodb.intervention_flow", CommandTimeout=#duration(0, 2, 0, 0)])
in
    Source

// DIM Intervention Channel
let
    Source = Table.Buffer(#"DIM Intervention Channel STG"),
    #"Filtered Rows" = Table.SelectRows(Source, each ([analytics_metadata] <> null)),
    #"Parsed JSON" = Table.TransformColumns(#"Filtered Rows",{{"analytics_metadata", Json.Document}}),
    #"Expanded analytics_metadata" = Table.ExpandRecordColumn(#"Parsed JSON", "analytics_metadata", {"decision", "recurring", "projectCode", "target_days", "planned_allocated", "experiment_start_date", "intervention_success_period", "goal"}, {"analytics_metadata.decision", "analytics_metadata.recurring", "analytics_metadata.projectCode", "analytics_metadata.target_days", "analytics_metadata.planned_allocated", "analytics_metadata.experiment_start_date", "analytics_metadata.intervention_success_period", "analytics_metadata.goal"}),
    #"Expanded analytics_metadata.decision" = Table.ExpandRecordColumn(#"Expanded analytics_metadata", "analytics_metadata.decision", {"decision_date", "decision_status", "decision_comment"}, {"analytics_metadata.decision.decision_date", "analytics_metadata.decision.decision_status", "analytics_metadata.decision.decision_comment"}),
    #"Expanded analytics_metadata.goal" = Table.ExpandRecordColumn(#"Expanded analytics_metadata.decision", "analytics_metadata.goal", {"adherence", "retention", "has_profile", "has_pharmacy_requests"}, {"analytics_metadata.goal.adherence", "analytics_metadata.goal.retention", "analytics_metadata.goal.has_profile", "analytics_metadata.goal.has_pharmacy_requests"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded analytics_metadata.goal",{"intervention_name", "active", "created", "payload", "analytics_metadata.decision.decision_date", "analytics_metadata.decision.decision_status", "analytics_metadata.decision.decision_comment", "analytics_metadata.recurring", "analytics_metadata.projectCode", "analytics_metadata.target_days", "analytics_metadata.planned_allocated", "analytics_metadata.experiment_start_date", "analytics_metadata.goal.adherence", "analytics_metadata.goal.retention", "analytics_metadata.goal.has_profile", "analytics_metadata.goal.has_pharmacy_requests", "type", "handler"}),
    #"Expanded analytics_metadata.intervention_success_period" = Table.ExpandListColumn(#"Removed Columns", "analytics_metadata.intervention_success_period"),
    #"Expanded analytics_metadata.intervention_success_period1" = Table.ExpandRecordColumn(#"Expanded analytics_metadata.intervention_success_period", "analytics_metadata.intervention_success_period", {"channel"}, {"analytics_metadata.intervention_success_period.channel"}),
    #"Filtered Rows1" = Table.SelectRows(#"Expanded analytics_metadata.intervention_success_period1", each ([analytics_metadata.intervention_success_period.channel] <> null)),
    #"Removed Duplicates" = Table.Distinct(#"Filtered Rows1"),
    #"Grouped Rows" = Table.Group(#"Removed Duplicates", {"id"}, {{"Count", each Text.Combine([analytics_metadata.intervention_success_period.channel],", "), type text}})
in
    #"Grouped Rows"

// DIM Intervention Goal STG
let
    Source = Table.Buffer(MySQL.Database(server, "analytics", [ReturnSingleDatabase=true, Query="SELECT * FROM medicodb.intervention_flow", CommandTimeout=#duration(0, 2, 0, 0)]))
in
    Source

// DIM Intervention Goal
let
    Source = Table.Buffer(#"DIM Intervention Goal STG"),
    #"Filtered Rows" = Table.SelectRows(Source, each ([analytics_metadata] <> null)),
    #"Parsed JSON" = Table.TransformColumns(#"Filtered Rows",{{"analytics_metadata", Json.Document}}),
    #"Expanded analytics_metadata" = Table.ExpandRecordColumn(#"Parsed JSON", "analytics_metadata", {"decision", "recurring", "projectCode", "target_days", "planned_allocated", "experiment_start_date", "intervention_success_period", "goal"}, {"analytics_metadata.decision", "analytics_metadata.recurring", "analytics_metadata.projectCode", "analytics_metadata.target_days", "analytics_metadata.planned_allocated", "analytics_metadata.experiment_start_date", "analytics_metadata.intervention_success_period", "analytics_metadata.goal"}),
    #"Expanded analytics_metadata.decision" = Table.ExpandRecordColumn(#"Expanded analytics_metadata", "analytics_metadata.decision", {"decision_date", "decision_status", "decision_comment"}, {"analytics_metadata.decision.decision_date", "analytics_metadata.decision.decision_status", "analytics_metadata.decision.decision_comment"}),
    #"Removed Other Columns" = Table.SelectColumns(#"Expanded analytics_metadata.decision",{"analytics_metadata.goal", "id"}),
    #"Expanded analytics_metadata.goal" = Table.ExpandRecordColumn(#"Removed Other Columns", "analytics_metadata.goal", {"adherence", "retention", "has_profile", "has_pharmacy_requests"}, {"adherence", "retention", "has_profile", "has_pharmacy_requests"}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Expanded analytics_metadata.goal", {"id"}, "Attribute", "Value"),
    #"Renamed Columns" = Table.RenameColumns(#"Unpivoted Columns",{{"Attribute", "Goal"}}),
    #"Grouped Rows" = Table.Group(#"Renamed Columns", {"id"}, {{"Goal", each Text.Combine([Goal],", "), type text}})
in
    #"Grouped Rows"

// FACT Intervention Experiments Allocation STG
let
    Source = Table.Buffer(MySQL.Database(server, "analytics", [ReturnSingleDatabase=true, Query="SELECT intervention_id, allocation_date FROM analytics.jiti_user_allocation WHERE intervention_id IN ("&Text.Combine(Table.Column(#"DIM Intevention" as table, "id_text" as text), "," )&")", CommandTimeout=#duration(0, 2, 0, 0)]))
in
    Source

// FACT Intervention Experiments Allocation
let
    Source = Table.Buffer( #"FACT Intervention Experiments Allocation STG"),
    #"Merged Queries" = Table.NestedJoin(Source, {"intervention_id"}, #"FACT Intervention", {"Intervention ID"}, "FACT Intervention For Join", JoinKind.Inner),
    #"Expanded FACT Intervention" = Table.ExpandTableColumn(#"Merged Queries", "FACT Intervention For Join", {"Target Days", "Planned Allocated"}, {"Target Days", "Planned Allocated"}),
    #"Added Custom" = Table.AddColumn(#"Expanded FACT Intervention", "on target", each if Duration.Days(DateTime.LocalNow() - [allocation_date]) > [Target Days] then 1 else 0),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Target Days"}),
    #"Grouped Rows" = Table.Group(#"Removed Columns", {"intervention_id"}, {{"Users", each Table.RowCount(_), type number}, {"First Allocation Date", each List.Min([allocation_date]), type datetime}, {"Last Allocation Date", each List.Max([allocation_date]), type datetime}, {"% of people that passed that target days we passed", each List.Average([on target]), type number}, {"Planned Allocated", each List.Max([Planned Allocated]), type number}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Grouped Rows",{{"% of people that passed that target days we passed", Percentage.Type}}),
    #"Inserted Division" = Table.AddColumn(#"Changed Type", "Division", each if [Planned Allocated] = 0 then null else [Users] / [Planned Allocated], type number),
    #"Changed Type1" = Table.TransformColumnTypes(#"Inserted Division",{{"Division", Percentage.Type}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type1",0,null,Replacer.ReplaceValue,{"Planned Allocated"}),
    #"Added Custom1" = Table.AddColumn(#"Replaced Value", "% users passed target days out of planned", each [#"% of people that passed that target days we passed"]*[Users]/[Planned Allocated]),
    #"Renamed Columns" = Table.RenameColumns(#"Added Custom1",{{"Division", "Allocated Actual/Plan"}}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Renamed Columns",{{"% users passed target days out of planned", Percentage.Type}}),
    #"Renamed Columns1" = Table.RenameColumns(#"Changed Type2",{{"% users passed target days out of planned", "Users Passed Target Days/Allocation Plan"}, {"Planned Allocated", "Planned allocated user count"}}),
    #"Added Conditional Column" = Table.AddColumn(#"Renamed Columns1", "Users Finished / Allocation Plan (Rounding >1 to 1)", each if [#"Users Passed Target Days/Allocation Plan"] >= 1 then 1 else [#"Users Passed Target Days/Allocation Plan"]),
    #"Replaced Errors" = Table.ReplaceErrorValues(#"Added Conditional Column", {{"Users Finished / Allocation Plan (Rounding >1 to 1)", null}}),
    #"Added Custom2" = Table.AddColumn(#"Replaced Errors", "Custom.1", each if [#"Users Finished / Allocation Plan (Rounding >1 to 1)"] is null then null else Duration.Days(DateTime.LocalNow() - [First Allocation Date])/[#"Users Finished / Allocation Plan (Rounding >1 to 1)"]),
    #"Changed Type3" = Table.TransformColumnTypes(#"Added Custom2",{{"Custom.1", type duration}}),
    #"Replaced Errors1" = Table.ReplaceErrorValues(#"Changed Type3", {{"Custom.1", null}}),
    #"Renamed Columns2" = Table.RenameColumns(#"Replaced Errors1",{{"Custom.1", "Estimated days till decision"}}),
    #"Extracted Days" = Table.TransformColumns(#"Renamed Columns2",{{"Estimated days till decision", Duration.Days, Int64.Type}}),
    #"Removed Columns1" = Table.RemoveColumns(#"Extracted Days",{"Users Finished / Allocation Plan (Rounding >1 to 1)"}),
    #"Renamed Columns3" = Table.RenameColumns(#"Removed Columns1",{{"intervention_id", "Intervention ID"}, {"% of people that passed that target days we passed", "% of users that passed the target days"}})
in
    #"Renamed Columns3"

// DIM Intervention Channel Detailed STG
let
    Source = Table.Buffer(MySQL.Database(server, "analytics", [ReturnSingleDatabase=true, Query="SELECT * FROM medicodb.intervention_flow", CommandTimeout=#duration(0, 2, 0, 0)]))
in
    Source

// DIM Intervention Channel Detailed
let
    Source = Table.Buffer(#"DIM Intervention Channel Detailed STG"),
    #"Filtered Rows" = Table.SelectRows(Source, each ([analytics_metadata] <> null)),
    #"Parsed JSON" = Table.TransformColumns(#"Filtered Rows",{{"analytics_metadata", Json.Document}}),
    #"Expanded analytics_metadata" = Table.ExpandRecordColumn(#"Parsed JSON", "analytics_metadata", {"decision", "recurring", "projectCode", "target_days", "planned_allocated", "experiment_start_date", "intervention_success_period", "goal"}, {"analytics_metadata.decision", "analytics_metadata.recurring", "analytics_metadata.projectCode", "analytics_metadata.target_days", "analytics_metadata.planned_allocated", "analytics_metadata.experiment_start_date", "analytics_metadata.intervention_success_period", "analytics_metadata.goal"}),
    #"Expanded analytics_metadata.decision" = Table.ExpandRecordColumn(#"Expanded analytics_metadata", "analytics_metadata.decision", {"decision_date", "decision_status", "decision_comment"}, {"analytics_metadata.decision.decision_date", "analytics_metadata.decision.decision_status", "analytics_metadata.decision.decision_comment"}),
    #"Expanded analytics_metadata.goal" = Table.ExpandRecordColumn(#"Expanded analytics_metadata.decision", "analytics_metadata.goal", {"adherence", "retention", "has_profile", "has_pharmacy_requests"}, {"analytics_metadata.goal.adherence", "analytics_metadata.goal.retention", "analytics_metadata.goal.has_profile", "analytics_metadata.goal.has_pharmacy_requests"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded analytics_metadata.goal",{"intervention_name", "active", "created", "payload", "analytics_metadata.decision.decision_date", "analytics_metadata.decision.decision_status", "analytics_metadata.decision.decision_comment", "analytics_metadata.recurring", "analytics_metadata.projectCode", "analytics_metadata.target_days", "analytics_metadata.planned_allocated", "analytics_metadata.experiment_start_date", "analytics_metadata.goal.adherence", "analytics_metadata.goal.retention", "analytics_metadata.goal.has_profile", "analytics_metadata.goal.has_pharmacy_requests", "type", "handler"}),
    #"Expanded analytics_metadata.intervention_success_period" = Table.ExpandListColumn(#"Removed Columns", "analytics_metadata.intervention_success_period"),
    #"Expanded analytics_metadata.intervention_success_period1" = Table.ExpandRecordColumn(#"Expanded analytics_metadata.intervention_success_period", "analytics_metadata.intervention_success_period", {"channel"}, {"analytics_metadata.intervention_success_period.channel"}),
    #"Filtered Rows1" = Table.SelectRows(#"Expanded analytics_metadata.intervention_success_period1", each ([analytics_metadata.intervention_success_period.channel] <> null)),
    #"Removed Duplicates" = Table.Distinct(#"Filtered Rows1"),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Duplicates",{{"id", "Intervention ID"}, {"analytics_metadata.intervention_success_period.channel", "Channel"}})
in
    #"Renamed Columns"

// Allocation Bins
let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcsnPS1XSUTJTitWJVvLLL1EILkksKklNAYoZgsVMDVR1zU1VgXxjMN/IVFUXKAbkG4H5QDldC7C8CZgPZOtaWoL4pkqxsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Stage = _t, Rank = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Stage", type text}, {"Rank", Int64.Type}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Stage", "Allocated Actual/Plan (Bins)"}})
in
    #"Renamed Columns"

// FACT Intervention For Join
let
    Source = #"FACT Intervention"
in
    Source

 

 

 

 

 

 

 

Will appreciate your support! 

 

here is the entire query code

 

Thanks, Barak

3 REPLIES 3
Anonymous
Not applicable

thank you for your responses. 

 

@TomMartens in the code there are STG queries which derive from the DB. All of the transformations happen on the DIM/FACT just as the guides, which I've read before suggest. Am I doing something wrong?

@v-yingjl I've set it to public (both file and data gateway) and still didn't work.

v-yingjl
Community Support
Community Support

Hi @Anonymous ,

Besides @ TomMartens mentioned, you can also refer the following simliar issues whether can help you:

  1. Unable to combine data - Please rebuild this data combination - Refresh  
  2. Refresh error when combining multiple Web queries  

 

Best Regards,
Yingjie Li

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

TomMartens
Super User
Super User

Hey @Anonymous ,

 

what you are facing is called the privacy firewall, you can read about it here:https://docs.microsoft.com/en-us/power-bi/admin/desktop-privacy-levels

There are many blogs outside how to to overcome this.

You may consider changing the settings of each data source

File --> Options and settings --> Data source settings --> Edit permissions

image.png
and the setting in the pbix

File --> Options and settings --> Options --> Current file --> Privacy

image.png

After you applied some changes, of course, you have to publish your report to the service once again.

 

Hopefully, this provides some insights on how to tackle your challenge.

 

Regards,

Tom

 



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors