Forum Discussion

Magnolia's avatar
Magnolia
Frequent Visitor
2 years ago
Solved

Load to Dataverse only when there are rows present in Power Query

I am trying to attempt an ETL of a kind with multiple Power tools. 

 

My main datasource is a foreign datamart X. The business needs to add two new columns to the dataset (status and date column) to indicate that they have reviewed it and will be assessing it on a "date". So these two columns cannot be added to the original source. 

 

I have loaded the identifiers from datamart X into a dataverse table and added the two columns. 

 

Then I have created a dataflow in PowerBI that compared original data source to the copy that I have in dataverse through right anti join and creates a "to load" table that later is picked up by dataflow (in power apps) and loaded into the dataverse table as new row. 
A user is then able to manage the dataverse table through an Canvas App embeded in Power BI report which also displays newest information from the original data source and the dataverse table. 

 

The issue is when there are no new rows and the right anti join returns empty table and because of the future (necessary) step returns an error that causes the flow to fail. 

Expression.Error: There weren't enough elements in the enumeration to complete the operation.

Details
Reason = Expression.Error
Detail = #table({"Number", "Planned Assesment", "SA Fulfilment", "dmn_demand"}, {})

The question here - is there a way to run the dataflow only where "right anti" returns rows? is there a way to replace this error with just an empty table, so that the flow runs but do not append any rows?  is there a simpler way to deal with this whole process? 

 

Need Help
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi Magnolia ,
    Based on your description, you are dealing with empty tables due to right-reverse joins in the Power BI data flow.
    Since you are already using Power Apps and Power BI, integrating Power Automate to conditionally trigger the data flow may be a viable solution. You can set up a Power Automate process that checks for rows in the results of a Power BI data flow. If a row exists, it goes ahead and triggers the data flow in Power Apps, loading the data into Dataverse. This approach allows you to bypass the steps that cause errors when no rows are returned.
    Using conditionals - Power Automate | Microsoft Learn
    Of course, to directly address the empty table issue in the Power BI data flow, consider implementing conditional logic in the M query in the Power Query Editor. You can use this function to check whether the result of a right-reverse join is an empty table. If it is, you can return a table structure with no rows instead of letting the process error out.
    You can try this code

    let
    Source = <RightAntiJoin>,
    Output = if Table.IsEmpty(Source) then #table(<Columns>, {}) else Source
in
    Output

     

    Best regards,
    Albert He

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

     

     

     



2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi Magnolia ,
    Based on your description, you are dealing with empty tables due to right-reverse joins in the Power BI data flow.
    Since you are already using Power Apps and Power BI, integrating Power Automate to conditionally trigger the data flow may be a viable solution. You can set up a Power Automate process that checks for rows in the results of a Power BI data flow. If a row exists, it goes ahead and triggers the data flow in Power Apps, loading the data into Dataverse. This approach allows you to bypass the steps that cause errors when no rows are returned.
    Using conditionals - Power Automate | Microsoft Learn
    Of course, to directly address the empty table issue in the Power BI data flow, consider implementing conditional logic in the M query in the Power Query Editor. You can use this function to check whether the result of a right-reverse join is an empty table. If it is, you can return a table structure with no rows instead of letting the process error out.
    You can try this code

    let
    Source = <RightAntiJoin>,
    Output = if Table.IsEmpty(Source) then #table(<Columns>, {}) else Source
in
    Output

     

    Best regards,
    Albert He

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