Forum Discussion

AXDevTim's avatar
AXDevTim
Frequent Visitor
1 year ago
Solved

Insert Table from Power BI Dataset into excel removes duplicate records

I think there is a BUG with the insert Table option for ingesting data into excel from Power BI Dataset.  It appears to aggregate rows with identical data, in effect deduping them.  

 

Steps to reproduce:

1. Create a Dataset that contains two identical rows

2. In excel, 'Insert Table' from Power BI dataset.  

 

You will see only 1 row, not 2.

 

Insert Pivot Table option does work correctly.  

 

Is there a parameter on the Dataset I am missing?  I do typically set all my numeric columns to 'None' aggregation. 

 

My simple solution is to add a unique identifer (rownum) to each line, but I feel like this should be fixed or I should learn what I did wrong.

 

Thanks, 

Tim

Need Help
  • OwenAuger's avatar
    OwenAuger
    1 year ago

    Hi AXDevTim 

    The Insert Table interface is designed to create an Excel table in the same form as a table visual would be created in a Power BI report containing the same columns/measures. This means it is summarized, so each distinct combination of column values appears once in the query result.

     

    If you want to return the "raw" table instead, one method would be:

    1. Create a table with Insert Table.
    2. Right click the table > Table > Edit Query
    3. In the Command Text box, change the query to EVALUATE YourTable
    4. Click OK

    Does this work for you?

     

2 Replies

  • OwenAuger's avatar
    OwenAuger
    Super User
    1 year ago

    Hi AXDevTim 

    The Insert Table interface is designed to create an Excel table in the same form as a table visual would be created in a Power BI report containing the same columns/measures. This means it is summarized, so each distinct combination of column values appears once in the query result.

     

    If you want to return the "raw" table instead, one method would be:

    1. Create a table with Insert Table.
    2. Right click the table > Table > Edit Query
    3. In the Command Text box, change the query to EVALUATE YourTable
    4. Click OK

    Does this work for you?

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi AXDevTim ,

     

    Thanks for reaching out to our community.

    Agree with OwenAuger. It display the summarized data in Power BI report by default.

    Please see below examples. The data displayed in Excel is the summarized data in the Power BI report, not the data in Power Query.

    In short, your approach works.

     

    Best Regards,

    Stephen Tao

     

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