Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
@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
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. 😞
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.