Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Dataflow automatically changes type for [Table] column, nulling all values

I've created a dataflow that simply pulls in data from selected Salesforce objects (parameterised so that we can swap between Production and Sandbox)

I've now set up a child dataflow that performs filtering and transformation. I've also set up parameters for the workspace and source dataflow.

To avoid repeating steps when pulling in the tables from the SFData dataflow, I have a base query:

 

let
  Source = PowerBI.Dataflows(null),
  FilterWorkspace = Table.SelectRows(Source, each [workspaceName] = DataFlowWorkspace),
  ExpandDataflows = Table.ExpandTableColumn(FilterWorkspace, "Data", {"dataflowId", "dataflowName", "Data"}, {"dataflowId", "dataflowName", "Data"}),
  FilterDataFlow = Table.SelectRows(ExpandDataflows, each [dataflowName] = DataFlowName),
  ExpandDataFlowObjects = Table.ExpandTableColumn(FilterDataFlow, "Data", {"entity", "Data"}, {"entity", "Data"}),
  RemoveOtherColumns = Table.SelectColumns(ExpandDataFlowObjects, {"entity", "Data"})
in
  RemoveOtherColumns

 

This leaves me with a list of table objects:

entityData
Account[Table]
Buyer Group Member[Table]
etc.etc.

I then reference to import the SFData tables, e.g.:

 

let
  Source = #"Dataflow Base",
  Navigation = Source{[entity = "Account"]}[Data]
in
  Navigation

 

The issue I have is that PBO requires that all columns must have a type, otherwise it can't be loaded. If I don't do anything, the Data column is converted to text, but generates an error (which then gets converted to a null). There doesn't appear to be a data type I can set the Data column to, to prevent this.

This means for every table I'm importing from the data flow (and any subsequent data flows), I need to use the full navigation steps to get to the entity list and then navigate to the object I want.

Is there a way around this?

Status: New
Comments
v-chuncz-msft
Community Support

@Anonymous 

 

You may try Json.FromValue and Binary.ToText to convert it to String data type, then use Binary.FromText and Json.Document to convert it back.