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
Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

May 23 2022 epsiode 5 without aka link.jpg

The Power BI Community Show

Welcome to the Power BI Community Show! Jeroen ter Heerdt talks about the importance of Data Modeling.

Power BI Dev Camp Session 22 with aka link 768x460.jpg

Check it out!

Mark your calendars and join us on Thursday, May 26 at 11a PDT for a great session with Ted Pattison!

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!