Left join and fulfill all columns

[jaryszek]

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

[ImkeF · Accepted 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"

[Greg_Deckler]

@jaryszek - In PQ, I believe you can select multiple columns to join on:

Use the Ctrl/Shift key to select multiple columns.

[jaryszek]

Thank you Greg, i am doing this. But still i have problem with getting result lin in picture above, 

can you please help? 

[jaryszek]

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

[jaryszek]

Anyone? 

[v-xuding-msft · Accepted solution]

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"

[ImkeF · Accepted 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"

[ImkeF]

Hi @jaryszek ,

have you been able to make this work?