Data loss while Importing powerBi data to excel

[s12saxen]

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?

[v-yuezhe-msft]

@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

[s12saxen]

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?

[v-yuezhe-msft]

@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.


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

[s12saxen]

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

[v-yuezhe-msft]

@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

[v-yuezhe-msft]

@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

[s12saxen]

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

[v-yuezhe-msft]

@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