Hi,
I m seeing the below error:
[Unable to combine data] Section1/fnGetDataFlowData/Source references other queries or steps, so it may not directly access a data source. Please rebuild this data combination.. The exception was raised by the IDbCommand interface
We have several data flows based on views specific to the Business Unit.
As data size for all BU would be very large and we have data flows specific to the BUs I thought it would be better to parameterise and bring in data flows based on the BU for which report is designed.
Step 1: Bring in a table that contains the list of all required dataflows (master data flow table is not enabled and the refresh disabled)
let
Source = PowerBI.Dataflows(null),
#"Filtered Rows" = Table.SelectRows(Source, each ([workspaceName] = "ABCD" or [workspaceName] = "EFGH")),
#"Expanded Data" = Table.ExpandTableColumn(#"Filtered Rows", "Data", {"dataflowId", "dataflowName"}, {"dataflowId", "dataflowName"}),
#"Removed Other Columns" = Table.SelectColumns(#"Expanded Data",{"workspaceId", "dataflowId", "dataflowName", "workspaceName"}),
#"Added Key" = Table.AddColumn(#"Removed Other Columns", "Key", each if(Text.Contains([dataflowName], "dailyinventory")) then "DI"
//else if(Text.Contains([dataflowName], "matbatch")) then "MB"
else if(Text.Contains([dataflowName], "monthlyinventory")) then "MI"
else if(Text.Contains([dataflowName], "material_movement")) then "MM"
else if(Text.Contains([dataflowName], "prod_cons_qty")) then "PC"
else if(Text.Contains([dataflowName], "quality")) then "QA"
else "", type text),
#"Filtered Required Data Flows" = Table.SelectRows(#"Added Key", each Text.Contains([workspaceName], @BU) and ([Key] <> "")),
#"Removed Columns" = Table.RemoveColumns(#"Filtered Required Data Flows",{"workspaceName"}),
#"Inserted Text After Delimiter" = Table.AddColumn(#"Removed Columns", "Text After Delimiter", each Text.AfterDelimiter([dataflowName], "_", 2), type text),
#"Renamed Columns" = Table.RenameColumns(#"Inserted Text After Delimiter",{{"Text After Delimiter", "entityName"}})
in
#"Renamed Columns"
Step 2: Use the above table with fnGetDataFlowData to filter and use the required table basaed on the key:
let
Source = (key as text) => let
DFSource = Table.Buffer(_DataFlowMasterList),
#"DFSource Rows" = Table.SelectRows(DFSource, each ([Key] = key)),
wId = #"DFSource Rows"[workspaceId]{0},
dId = #"DFSource Rows"[dataflowId]{0},
eId = #"DFSource Rows"[entityName]{0},
Source = PowerBI.Dataflows(null),
Workspace = Source{[workspaceId=wId]}[Data],
DataFlow =Workspace{[dataflowId=dId]}[Data],
Data = DataFlow{[entity=eId]}[Data]
in
Data
in
Source
While this works fine on PBI Desktop, Data refresh is failing on PBI Service.
Kindly help.
Thans & Regards,
Mannu
