cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Ferenco
Regular Visitor

Big Issue with Query Merge

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:

 

WeekCustomer% ReadyOwnerProject Id
wk10y21Coca-cola20%J. Smith7755
wk10y21Pepsi10%M. Willis3341
wk11y21Coca-cola30%J. Smith7755
wk11y21Pepsi15%M. Willis3341

 

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:

Ferenco_0-1630654466684.png

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

Ferenco_1-1630654533335.png

Or like that:

Ferenco_2-1630654569672.png

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.

 

 

5 REPLIES 5
V-pazhen-msft
Community Support
Community Support

@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
Super User
Super User

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

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

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

@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?

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.

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.