In today's discussion, we will explore a technique in Power Query that allows us to extract multiple tables simultaneously. Specifically, we aim to combine the fSales table with the dManager table using the region column and incorporate the manager's name into the fSales table.
We can extract 2 tables as 2 queries and merge,
Query-1 to extract dimension table.
let
Source = Excel.CurrentWorkbook(){[Name="dManager"]}[Content],
ChangedType = Table.TransformColumnTypes(Source,{{"Zone", type text}, {"Manager", type text}})
in
ChangedType
Query-2 to extract fact table and do merge.
let
Source = Excel.CurrentWorkbook(){[Name="fSales"]}[Content],
Result = Table.Join(Source,"Region",Dim,"Zone",JoinKind.LeftOuter)
in
Result
Or
We can extract 2 tables as 2 steps in single query and merge.
let
FactTbl = Excel.CurrentWorkbook(){[Name="fSales"]}[Content],
DimTbl = Excel.CurrentWorkbook(){[Name="dManager"]}[Content],
Result = Table.Join(FactTbl,"Region",DimTbl,"Zone",JoinKind.LeftOuter)
in
Result
However, each of the two methods mentioned above necessitates a minimum of three steps.
I'll demonstrate a technique that allows you to complete this task in just two steps.
let
Source = each Excel.CurrentWorkbook(){[Name=_]}[Content],
Result = Table.Join(Source("fSales"),"Region",Source("dManager"),"Zone",1)
in
Result
This approach involves transforming the Source step into a function by prefixing each with the Excel.CurrentWorkbook function and substituting the table name with an
underscore (_). Source("fSales") retrieves the fact table, while Source("dManager") retrieves the dimension table. Subsequently, the Table.Join function is utilized to merge these tables and extract the manager column.
Comments