Forum Discussion

s12saxen's avatar
s12saxen
Frequent Visitor
8 years ago

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?

Need Help
Tutorial Requests

8 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    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

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    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's avatar
      s12saxen
      Frequent Visitor
      8 years ago

      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?

      • Anonymous's avatar
        Anonymous
        Not applicable
        8 years ago

        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