Forum Discussion

Anonymous

Not applicable

5 years ago

Solved

Power Query Append Queries overwrites data

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

Need Help

Anonymous

5 years ago

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"

Anonymous

Not applicable

5 years ago

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

Community Champion

5 years ago

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

Not applicable

5 years ago

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

Community Champion

5 years ago

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

Anonymous

Not applicable

5 years ago

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"

Forum Discussion

Power Query Append Queries overwrites data

Related content

Power Query/Overwrite cell question

Copy Job OVERWRITE

Copy Job deceivingly says "Overwrite" when some tables are "Append"

Power Query - Append Latest FX Rates Without Overwriting Previous Months

Copy data into lakehouse and warehouse - append or overwrite