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