cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
s12saxen Frequent Visitor
Frequent Visitor

Data loss while Importing powerBi data to excel

Hi,

I have two excel files (File1 and File20. File1 has 1070 rows, and file2 has about 11000 rows. I moved them to powerbi and merged them (Outer left, so all rows from file1 should be there and only matching from file2). The merging seems ok, but when i try to move this to excel - I do the "copy the entire table" and then paste on excel, i only see 1001 rows...why am I having data loss and how do i correct it so i see all 1070 rows?

8 REPLIES 8
Moderator v-yuezhe-msft
Moderator

Re: Data loss while Importing powerBi data to excel

@s12saxen,

I am unable to reproduce the issue using my sample file. Would you please share me the Excel files so that I can test? You can send the Excel files via Private Message.

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Moderator v-yuezhe-msft
Moderator

Re: Data loss while Importing powerBi data to excel

@s12saxen

Thanks for your sharing. Based on my test in December update of Power BI Desktop, when we don't extract values from merged table, the merged table has 1070 rows, when we extract values from the merged table, the table has 1246 rows. This is caused by that the relationship between File1 and File2 is one to many.

You can make a simple test by adding blank query by pasting the following code.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSlTSUUo0VIrVgTKNwMwkIDPJEMFEEjVGME2UYmMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}})
in
    #"Changed Type"
let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSlSK1YlWSgKTyWAyRSk2FgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}})
in
    #"Changed Type"

 

let
    Source = Table.NestedJoin(Table1,{"Column1"},Table2,{"Column1"},"Table2",JoinKind.LeftOuter),
    #"Expanded Table2" = Table.ExpandTableColumn(Source, "Table2", {"Column1", "Column2"}, {"Table2.Column1", "Table2.Column2"})
in
    #"Expanded Table2"




Regards,
Lydia

 

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
s12saxen Frequent Visitor
Frequent Visitor

Re: Data loss while Importing powerBi data to excel

What does this query actually do? I'd like to be able to export all 1070 rows of data into excel...how can I do that?

Moderator v-yuezhe-msft
Moderator

Re: Data loss while Importing powerBi data to excel

@s12saxen

Have you tested the above simple query? When you don't extract value from Table2 column in the merged table, the row number of the merged table is same as the row number of first table. When you extract values, the merged table return more rows because the relationship of column1 fields between two tables is 1:many.
1.JPG

In your scenario, 1070 rows are contained in the 1246 rows, as the relationship of join fields between the two tables is 1: many, you get 1246 rows when you extract values in  the merged table.

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
s12saxen Frequent Visitor
Frequent Visitor

Re: Data loss while Importing powerBi data to excel

How do I get only the 1070 rows from table1, with all matching fields only from table2? I thought thats what left outer join does? I also want to be able to get this data in excel

s12saxen Frequent Visitor
Frequent Visitor

Re: Data loss while Importing powerBi data to excel

I am able to get the right #$ of rows in powerbi (1070), my concern right now is how do i move this data in excel withous losing it? When I copy the entire table right now to excel it only copies 1000 rows not 1070...please help

Moderator v-yuezhe-msft
Moderator

Re: Data loss while Importing powerBi data to excel

@s12saxen

Right click the merged table in Data view and select "Copy Table", then use "Ctrl+V" to paste the data of the table to Excel, I get all 1246 rows with this method.

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Moderator v-yuezhe-msft
Moderator

Re: Data loss while Importing powerBi data to excel

@s12saxen,

Do you use December update of Power BI Desktop? Please clear cache in your Power BI Desktop, reboot machine and check if you get all rows.

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Power BI Helps Homeless and Trouble Youth

Power BI Helps Homeless and Trouble Youth

We spoke with Power BI Super User, Greg Deckler, about his charity work

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 260 members 2,997 guests
Please welcome our newest community members: