Forum Discussion

jaryszek's avatar
jaryszek
Super User
5 years ago
Solved

Left join and fulfill all columns

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

 

 

Need Help
  • ImkeF's avatar
    ImkeF
    5 years ago

    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"

     

  • v-xuding-msft's avatar
    v-xuding-msft
    5 years ago

    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"

     

     

7 Replies