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
2022 Release Wave 1 760x460.png

2022 Release Wave 1 Plan

Power Platform release plan for the 2022 release wave 1 describes all new features releasing from April 2022 through September 2022.

Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors