Forum Discussion
Merge Vs Natural Join
Hi All,
I have 3 differrent tables , all are connected with builtin relationships
to achieve my desired output , I created a dax table
Result = SELECTCOLUMNS((NATURALINNERJOIN(BookingsWO,Resources)),"WO",BookingsWO[Work Order],"DESC",BookingsWO[Work Order Summary],"TYPE",Resources[Category],"NAME",Resources[Name],"START",BookingsWO[Start Date],"END",BookingsWO[End Date],"JOB",BookingsWO[JobNo],"CUSTOMER",BookingsWO[Service Account],
"MASON",if(Resources[Category]="MASON",1,0),
"PAINTER",if(Resources[Category]="PAINTER",1,0),
"PLUMBER",if(Resources[Category]="PLUMBER",1,0),
"ELECTRICIAN",if(Resources[Category]="ELECTRICIAN",1,0),
"CARPENTER",if(Resources[Category]="CARPENTER",1,0),
"AC",IF(Resources[Category]="AC",1,0))
and i'm able to acheive the result.
My question is , is this the right way to achieve this ? or should i make use of Power query merge (transform tools with pivot)
are there any disadvantages in this way?
anybody kindly guide
Thanks for helping!
Hi dejadpower ,
Thanks lbendlin for the prompt reply. I have created some data to solve this issue based on the information you provided and you can follow the steps below:
1.In Power Query.let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCvc3MDAwVNJRCkotzi8tSk5VAHGMDIxM9I30jRBMY6VYHWyqTRFKTJCZcNVGyKqRDDRDMM1xqDbEarYFDtUoSrCpNkZWbYzVbEMDhHITHE4xQ2bGxgIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Work order" = _t, Resource = _t, #"Start Time" = _t, #"End Time" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Work order", type text}, {"Resource", type text}, {"Start Time", type date}, {"End Time", type date}}), #"Appended Query" = Table.Combine({#"Changed Type", #"Resource Table"}), #"Removed Alternate Rows" = Table.AlternateRows(#"Appended Query",7,5,13), #"Removed Columns" = Table.RemoveColumns(#"Removed Alternate Rows",{"Resource Category"}), #"Merged Queries" = Table.NestedJoin(#"Removed Columns", {"Resource"}, #"Resource Table", {"Resource"}, "Resource Table", JoinKind.LeftOuter), #"Expanded Resource Table" = Table.ExpandTableColumn(#"Merged Queries", "Resource Table", {"Resource Category"}, {"Resource Table.Resource Category"}), #"Pivoted Column" = Table.Pivot(#"Expanded Resource Table", List.Distinct(#"Expanded Resource Table"[#"Resource Table.Resource Category"]), "Resource Table.Resource Category", "Resource", List.Count), #"Changed Type1" = Table.TransformColumnTypes(#"Pivoted Column",{{"Painter", Int64.Type}, {"Mason", Int64.Type}, {"Electrician", Int64.Type}, {"Plumber", Int64.Type}}) in #"Changed Type1"2.Add new Measures.
Start time_ = VAR _1 = SELECTEDVALUE( 'Bookings Table'[Work order] ) CALCULATE ( MIN ( 'Bookings Table'[Start Time] ), FILTER ( ALL ( 'Bookings Table' ), 'Bookings Table'[Work order] = _1 ) )End time_ = VAR _1 = SELECTEDVALUE( 'Bookings Table'[Work order] ) CALCULATE ( MAX ( 'Bookings Table'[End Time] ), FILTER ( ALL ( 'Bookings Table' ), 'Bookings Table'[Work order] = _1 ) )Days = DATEDIFF ( [Start time_], [End time_], DAY ) + 1Fianl output:
How to Get Your Question Answered Quickly - Microsoft Fabric Community
If it does not help, please provide more details with your desired out put and pbix file without privacy information.
Best Regards,
Ada Wang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
6 Replies
Hi dejadpower ,
Thanks lbendlin for the prompt reply. I have created some data to solve this issue based on the information you provided and you can follow the steps below:
1.In Power Query.let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCvc3MDAwVNJRCkotzi8tSk5VAHGMDIxM9I30jRBMY6VYHWyqTRFKTJCZcNVGyKqRDDRDMM1xqDbEarYFDtUoSrCpNkZWbYzVbEMDhHITHE4xQ2bGxgIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Work order" = _t, Resource = _t, #"Start Time" = _t, #"End Time" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Work order", type text}, {"Resource", type text}, {"Start Time", type date}, {"End Time", type date}}), #"Appended Query" = Table.Combine({#"Changed Type", #"Resource Table"}), #"Removed Alternate Rows" = Table.AlternateRows(#"Appended Query",7,5,13), #"Removed Columns" = Table.RemoveColumns(#"Removed Alternate Rows",{"Resource Category"}), #"Merged Queries" = Table.NestedJoin(#"Removed Columns", {"Resource"}, #"Resource Table", {"Resource"}, "Resource Table", JoinKind.LeftOuter), #"Expanded Resource Table" = Table.ExpandTableColumn(#"Merged Queries", "Resource Table", {"Resource Category"}, {"Resource Table.Resource Category"}), #"Pivoted Column" = Table.Pivot(#"Expanded Resource Table", List.Distinct(#"Expanded Resource Table"[#"Resource Table.Resource Category"]), "Resource Table.Resource Category", "Resource", List.Count), #"Changed Type1" = Table.TransformColumnTypes(#"Pivoted Column",{{"Painter", Int64.Type}, {"Mason", Int64.Type}, {"Electrician", Int64.Type}, {"Plumber", Int64.Type}}) in #"Changed Type1"2.Add new Measures.
Start time_ = VAR _1 = SELECTEDVALUE( 'Bookings Table'[Work order] ) CALCULATE ( MIN ( 'Bookings Table'[Start Time] ), FILTER ( ALL ( 'Bookings Table' ), 'Bookings Table'[Work order] = _1 ) )End time_ = VAR _1 = SELECTEDVALUE( 'Bookings Table'[Work order] ) CALCULATE ( MAX ( 'Bookings Table'[End Time] ), FILTER ( ALL ( 'Bookings Table' ), 'Bookings Table'[Work order] = _1 ) )Days = DATEDIFF ( [Start time_], [End time_], DAY ) + 1Fianl output:
How to Get Your Question Answered Quickly - Microsoft Fabric Community
If it does not help, please provide more details with your desired out put and pbix file without privacy information.
Best Regards,
Ada Wang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
thank you Anonymous . I'll try your suggestion and update here.
As per your suggestion, Power query is more preferrable than DAX ?
thank you, i TRIED your power query version, it works, one last thing pending is I need help in calculating the date difference duration for each work order. i have already posted a question, can you suggest a workaround in this
What is preventing you from wiring these tables up in the data model?
i'm an absolute beginner in power bi, so i have confusion between choosing dax or power query
The preference is to use neither, but use the data model instead.
After that it is a question of mutability. If your results are impacted by filter choices then you need a DAX measure. If not then you can create a DAX calculated column or do it in Power Query.
