Big Issue with Query Merge

[Ferenco]

Dear Team,

I have one very-very strange issue with M Query Merge. It is not work properly. 2 weeks, I am sleeping bad, read this formu, but I can't fix this issue. So, let's go step by step:

1. We have a SQL database, which contain table, where every week we upload actual status of projects:

Week	Customer	% Ready	Owner	Project Id
wk10y21	Coca-cola	20%	J. Smith	7755
wk10y21	Pepsi	10%	M. Willis	3341
wk11y21	Coca-cola	30%	J. Smith	7755
wk11y21	Pepsi	15%	M. Willis	3341

2. Using this flat table I want to create start schema so - folowing next steps (For example for customers) I create a DIM table:

let
Source = Sql.Databases("XXXXXXX\KPI"),
KPIMS = Source{[Name="XXXXX"]}[Data],
dbo_IX_PCMSProjectDetailsHist = KPIMS{[Schema="dbo",Item="IX_PCMSProjectDetailsHist"]}[Data],
#"Changed Type" = Table.TransformColumnTypes(dbo_IX_PCMSProjectDetailsHist,{{"InsertTS", type date}}),
#"Filtered Rows - Site = Ukraine" = Table.SelectRows(#"Changed Type", each ([Site] = "Ukraine")),
Customer1 = #"Filtered Rows - Site = Ukraine"[Customer],
#"Removed Duplicates" = List.Distinct(Customer1),
#"Converted to Table" = Table.FromList(#"Removed Duplicates", Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Added Index" = Table.AddIndexColumn(#"Converted to Table", "Index", 1, 1, Int64.Type),
#"Renamed Columns" = Table.RenameColumns(#"Added Index",{{"Column1", "Customer"}, {"Index", "Index_Customer"}}),
#"Removed Errors" = Table.RemoveRowsWithErrors(#"Renamed Columns", {"Customer"}),
#"Buffer_Csutomers" = Table.Buffer(#"Removed Errors")
in
#"Buffer_Csutomers"

3. After tha, I merge queries ith my FACT Table:

let
Source = Sql.Databases("XXXXXXX\KPI"),
KPIMS = Source{[Name="XXXXX"]}[Data],
dbo_IX_PCMSProjectDetailsHist = KPIMS{[Schema="dbo",Item="IX_PCMSProjectDetailsHist"]}[Data],
#"Changed Type" = Table.TransformColumnTypes(dbo_IX_PCMSProjectDetailsHist,{{"InsertTS", type date}}),
#"Filtered Rows - Site = Ukraine" = Table.SelectRows(#"Changed Type", each ([Site] = "Ukraine")),
Customer1 = #"Filtered Rows - Site = Ukraine"[Customer],
#"Removed Duplicates" = List.Distinct(Customer1),
#"Converted to Table" = Table.FromList(#"Removed Duplicates", Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Added Index" = Table.AddIndexColumn(#"Converted to Table", "Index", 1, 1, Int64.Type),
#"Renamed Columns" = Table.RenameColumns(#"Added Index",{{"Column1", "Customer"}, {"Index", "Index_Customer"}}),
#"Removed Errors" = Table.RemoveRowsWithErrors(#"Renamed Columns", {"Customer"}),
#"Buffer_Csutomers" = Table.Buffer(#"Removed Errors")
in
#"Buffer_Csutomers"

3. In the Qeury editor everthing looks fine, but after each refresh (In desctop application or PBI web server) evrytime, I have wrong asigning of customers. There are no any logic, looks like after every time it is asing random number for customer:

Shold be:

What I receive after each re-fresh (Combination can be different every time):

Or like that:

I don't wan't to go back to using flat table vs. star-schema, but rigth now, the merge functionality is not works for me.

What I tied:

1) Trim, Clean, Upper case everthing beffore merge (First merge eveytime is good, after re-fresh I start to see mismaching);

2) Buffer table (In my case it is not help).

I will be very hapy, if somebody could advise a solution, how to deal with that.

P.S. Sorry for mistakes, I am not a native speeker.

Take care.

[V-pazhen-msft]

@Ferenco 
I failed to reproduce the model based on the steps you provide by editing the query. Can you create a pbix file with entered data as datasource. So we can go through the applied steps in detail. 

Best

Paul

[wdx223_Daniel]

how about revise the code of your getting customer list to this?

#"Removed Duplicates" = List.Buffer(List.Distinct(Customer1))

[Ferenco]

No, In Desktop Version this solution is also not working well. 😞

[Ferenco]

@wdx223_Daniel 

I tried. Looks like it is works fine in desctop application. But, in web service after each re-fresh I have new random values for customer column. 😞 Any other ideas? Should I keep #"Buffer_Csutomers" in the end of Query?

[Ferenco]

Thanks for Advice.

I will try. Why you think that should help? Why just regular "Remove dublicates" not wok properly? I would like to understand the logic.