Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi Guys,
i have 2 tables:
1) Soure Table:
2) Table which i want to left join:
3) and what i want to achevie:
as you can see i only Added "Funct3" to column Function and populate with the same data "VolemuID" and "ComponentInstance".
But i do not know how to do this in PQ:
Can anybody help?
https://drive.google.com/file/d/1lbbqB3IPMIPUvIZLifM_iojdSP3Nw7Ge/view?usp=sharing
Best,
Jacek
Solved! Go to Solution.
Hi @jaryszek ,
please check if this is giving you the desired results:
// t_Source
let
Source = Excel.CurrentWorkbook(){[Name="t_Source"]}[Content],
#"Merged Queries" = Table.NestedJoin(Source, {"Topology", "Tier"}, t_LeftJoin, {"Topology", "Tier"}, "t_LeftJoin", JoinKind.LeftOuter),
#"Expanded t_LeftJoin" = Table.ExpandTableColumn(#"Merged Queries", "t_LeftJoin", {"Function"}, {"t_LeftJoin.Function"}),
#"Removed Columns" = Table.RemoveColumns(#"Expanded t_LeftJoin",{"Function"}),
Joined = Table.RenameColumns(#"Removed Columns",{{"t_LeftJoin.Function", "Function"}}),
Custom1 = Joined & Source,
#"Removed Duplicates" = Table.Distinct(Custom1)
in
#"Removed Duplicates"
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
Hi @jaryszek ,
You also could try this:
let
Source = Excel.Workbook(File.Contents("C:\Users\xueding\Downloads\Temp testing only\LeftJoinExample.xlsx"), null, true),
t_Source_Table = Source{[Item="t_Source",Kind="Table"]}[Data],
#"Changed Type" = Table.TransformColumnTypes(t_Source_Table,{{"Topology", type text}, {"Tier", type text}, {"Function", type text}, {"VolumeID", Int64.Type}, {"ComponentInstance", Int64.Type}}),
#"Appended Query" = Table.Combine({#"Changed Type", t_LeftJoin}),
#"Removed Duplicates" = Table.Distinct(#"Appended Query", {"Topology", "Tier", "Function"}),
#"Sorted Rows1" = Table.Sort(#"Removed Duplicates",{{"Topology", Order.Ascending}, {"Tier", Order.Ascending}, {"Function", Order.Ascending}}),
#"Added Index" = Table.AddIndexColumn(#"Sorted Rows1", "Index", 1, 1, Int64.Type),
#"Added Index1" = Table.AddIndexColumn(#"Added Index", "Index.1", 0, 1, Int64.Type),
#"Renamed Columns" = Table.RenameColumns(#"Added Index1",{{"Index.1", "Previous Index"}}),
#"Merged Queries" = Table.NestedJoin(#"Renamed Columns", {"Previous Index"}, #"Renamed Columns", {"Index"}, "Renamed Columns", JoinKind.LeftOuter),
#"Expanded Renamed Columns" = Table.ExpandTableColumn(#"Merged Queries", "Renamed Columns", {"Topology", "Tier", "VolumeID", "ComponentInstance"}, {"Renamed Columns.Topology", "Renamed Columns.Tier", "Renamed Columns.VolumeID", "Renamed Columns.ComponentInstance"}),
#"Added Custom" = Table.AddColumn(#"Expanded Renamed Columns", "Custom", each if [Topology] = [Renamed Columns.Topology] and [Tier] = [Renamed Columns.Tier] and [VolumeID] = null then [Renamed Columns.VolumeID] else [VolumeID]),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each if [Topology] = [Renamed Columns.Topology] and [Tier] = [Renamed Columns.Tier] and [ComponentInstance] = null then [Renamed Columns.ComponentInstance]else [ComponentInstance]),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"VolumeID", "ComponentInstance", "Index", "Previous Index", "Renamed Columns.Topology", "Renamed Columns.Tier", "Renamed Columns.VolumeID", "Renamed Columns.ComponentInstance"}),
#"Renamed Columns1" = Table.RenameColumns(#"Removed Columns",{{"Custom", "VolumeID"}, {"Custom.1", "ComponentInstance"}})
in
#"Renamed Columns1"
@jaryszek - In PQ, I believe you can select multiple columns to join on:
Use the Ctrl/Shift key to select multiple columns.
Thank you Greg, i am doing this. But still i have problem with getting result lin in picture above,
can you please help?
I added new Topo to show you:
and please use my example from google drive:
https://drive.google.com/file/d/1lbbqB3IPMIPUvIZLifM_iojdSP3Nw7Ge/view?usp=sharing
you see what is happening?
I am using Topolgoy and Tier as key but want to pupulate with values from table1...
Jacek
Anyone?
Hi @jaryszek ,
You also could try this:
let
Source = Excel.Workbook(File.Contents("C:\Users\xueding\Downloads\Temp testing only\LeftJoinExample.xlsx"), null, true),
t_Source_Table = Source{[Item="t_Source",Kind="Table"]}[Data],
#"Changed Type" = Table.TransformColumnTypes(t_Source_Table,{{"Topology", type text}, {"Tier", type text}, {"Function", type text}, {"VolumeID", Int64.Type}, {"ComponentInstance", Int64.Type}}),
#"Appended Query" = Table.Combine({#"Changed Type", t_LeftJoin}),
#"Removed Duplicates" = Table.Distinct(#"Appended Query", {"Topology", "Tier", "Function"}),
#"Sorted Rows1" = Table.Sort(#"Removed Duplicates",{{"Topology", Order.Ascending}, {"Tier", Order.Ascending}, {"Function", Order.Ascending}}),
#"Added Index" = Table.AddIndexColumn(#"Sorted Rows1", "Index", 1, 1, Int64.Type),
#"Added Index1" = Table.AddIndexColumn(#"Added Index", "Index.1", 0, 1, Int64.Type),
#"Renamed Columns" = Table.RenameColumns(#"Added Index1",{{"Index.1", "Previous Index"}}),
#"Merged Queries" = Table.NestedJoin(#"Renamed Columns", {"Previous Index"}, #"Renamed Columns", {"Index"}, "Renamed Columns", JoinKind.LeftOuter),
#"Expanded Renamed Columns" = Table.ExpandTableColumn(#"Merged Queries", "Renamed Columns", {"Topology", "Tier", "VolumeID", "ComponentInstance"}, {"Renamed Columns.Topology", "Renamed Columns.Tier", "Renamed Columns.VolumeID", "Renamed Columns.ComponentInstance"}),
#"Added Custom" = Table.AddColumn(#"Expanded Renamed Columns", "Custom", each if [Topology] = [Renamed Columns.Topology] and [Tier] = [Renamed Columns.Tier] and [VolumeID] = null then [Renamed Columns.VolumeID] else [VolumeID]),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each if [Topology] = [Renamed Columns.Topology] and [Tier] = [Renamed Columns.Tier] and [ComponentInstance] = null then [Renamed Columns.ComponentInstance]else [ComponentInstance]),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"VolumeID", "ComponentInstance", "Index", "Previous Index", "Renamed Columns.Topology", "Renamed Columns.Tier", "Renamed Columns.VolumeID", "Renamed Columns.ComponentInstance"}),
#"Renamed Columns1" = Table.RenameColumns(#"Removed Columns",{{"Custom", "VolumeID"}, {"Custom.1", "ComponentInstance"}})
in
#"Renamed Columns1"
Hi @jaryszek ,
please check if this is giving you the desired results:
// t_Source
let
Source = Excel.CurrentWorkbook(){[Name="t_Source"]}[Content],
#"Merged Queries" = Table.NestedJoin(Source, {"Topology", "Tier"}, t_LeftJoin, {"Topology", "Tier"}, "t_LeftJoin", JoinKind.LeftOuter),
#"Expanded t_LeftJoin" = Table.ExpandTableColumn(#"Merged Queries", "t_LeftJoin", {"Function"}, {"t_LeftJoin.Function"}),
#"Removed Columns" = Table.RemoveColumns(#"Expanded t_LeftJoin",{"Function"}),
Joined = Table.RenameColumns(#"Removed Columns",{{"t_LeftJoin.Function", "Function"}}),
Custom1 = Joined & Source,
#"Removed Duplicates" = Table.Distinct(Custom1)
in
#"Removed Duplicates"
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
Hi @jaryszek ,
have you been able to make this work?
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.