Power Query Append Queries overwrites data

[Anonymous]

Hi everyone,

I got a PowerBI dataset with different queries. In Power Query editor I would like to append two queries (one contains data from the SAP data source, the other one contains data from an Excel worksheet). Both have the same columns.

However Power Query is overwriting the SAP data as I append the Excel query. How/why does this happen?

Best regards

Janina

[FarhanAhmed]

Can you please explain it with some sample data as to what do you mean by Overwriting?

Is it any duplicate data that is being overwritten?  

[Anonymous]

It is a customer dimension table. There are some duplicates regarding the customer code, however some of the columns of SAP data are not yet filled whereas those columns of the corresponding customers of Excel data are filled. So PowerBI should not be able the identify them as duplicates as it does not know which one is the primary key column.

However after appending, all SAP data is gone, even those with different customer code.

In this example data the first two rows would come from SAP, the last two from Excel. I would expect this result.

Customer Code	Customer Level 1	Postal Code
12345		7878
I9870		999
12345	123	7878
67890	678	1111

However the result is this, so only the Excel data:

Customer Code	Customer Level 1	Postal Code
12345	123	7878
67890	678	1111

[FarhanAhmed]

append queries should work fine for this dataset and return you (NxM) rows unless you are doing Merge Queries that will give you result for Left Outer Join.

[Anonymous]

Unfortunately it does not give the expected result. It's apparently doing a right outer join. However I did use "Append Queries", I did not use "Merge Queries"!

[FarhanAhmed]

If possible can you please share your M(Power Query) Code?

[Anonymous · Accepted solution]

Yesterday I implemented a workaround (rename the two Customer Code columns so they would not be appended but create two different columns for each source in the result table. Then merge them as one column).

Somehow today it seems to be working fine even without the workaround... No explanation why, I did not change anything. This is my code anyway

let
  Quelle = SapBusinessWarehouse.Cubes("xxxxxx", "xxx", "xxx", [Implementation="2.0"]),
  #"$INFOCUBE" = Quelle{[Name = "$INFOCUBE"]}[Data],
  #"$/ERP/SFIN_V01" = #"$INFOCUBE"{[Id = "$/ERP/SFIN_V01"]}[Data],
  
  #"Hinzugefügte Elemente" = Cube.Transform(#"$/ERP/SFIN_V01",
        {
            {Cube.AddAndExpandDimensionColumn, "[/ERP/CUSTOMER]", {"[/ERP/CUSTOMER].[LEVEL01]"}, {"Customer.Customer Level 01"}},
            {Table.AddColumn, "Customer.Customer Level 01.Key", each Cube.AttributeMemberProperty([Customer.Customer Level 01], "[2/ERP/CUSTOMER]")},
            {Table.AddColumn, "Customer.Customer Level 01.Country (Key)", each Cube.AttributeMemberProperty([Customer.Customer Level 01], "[2/ERP/COUNTRY]")},
            {Table.AddColumn, "Customer.Customer Level 01.City (Key)", each Cube.AttributeMemberProperty([Customer.Customer Level 01], "[2CITY]")},
            {Table.AddColumn, "Customer.Customer Level 01.SalesRep (Key)", each Cube.AttributeMemberProperty([Customer.Customer Level 01], "[2/SFX/ZZ1_Z2SF0]")},
            {Table.AddColumn, "Customer.Customer Level 01.ZIP (Key)", each Cube.AttributeMemberProperty([Customer.Customer Level 01], "[2ZIPCODE]")}
            
        }),
    #"Umbenannte Spalten" = Table.RenameColumns(
    #"Hinzugefügte Elemente",
    {
      {"Customer.Customer Level 01", "Customer"},
      {"Customer.Customer Level 01.Key", "Customer Code"},
      {"Customer.Customer Level 01.Country (Key)", "Country Code"},
      {"Customer.Customer Level 01.ZIP (Key)", "Postal Code"},
      {"Customer.Customer Level 01.City (Key)", "City"},
      {"Customer.Customer Level 01.SalesRep (Key)", "Sales Rep Code"}
    }
  ),
  #"Added Custom1" = Table.AddColumn(#"Umbenannte Spalten", "Customer Level 1 Code", each ""),
  #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Customer Level 1", each ""),
  #"Reordered Columns" = Table.ReorderColumns(#"Added Custom2",
    {
      "Customer Code",
      "Customer",
      "Customer Level 1 Code",
      "Customer Level 1",
      "Postal Code",
      "City",
      "Sales Rep Code",
      "Country Code"
    }
  ),
    #"Appended Query" = Table.Combine({#"Reordered Columns", #"HIST Dim Customer (2)"}),
    #"Added Custom" = Table.AddColumn(#"Appended Query", "Postal Code 2", each Text.Start([Postal Code],2)),
    #"Removed Duplicates" = Table.Distinct(#"Added Custom", {"Customer Code"})
in
    #"Removed Duplicates"