Forum Discussion

BugmanJ's avatar
BugmanJ
Icon for Helper V rankHelper V
3 years ago
Solved

Fill in Missing Gaps when Joining Data

Good Morning,

I have two tables like below:

Table 1 is from one source

KeyNameDate
ABob01/01/23
BSmith05/01/23
EJones08/01/23


Table 2 is from a directy query source from which at present I am forming via a summerised table

KeyPayment
A1
B2
C3
D4
E5


I need to create the table where the gaps in the NAME column from Table 1 are filled in with "unknown"

Table1 KeyNameDateTable2KeyPayment
ABob01/01/23A1
BSmith05/01/23B2
 Unknown C3
EJones08/01/23D4
 Unknown E5


How do i do this please?

Thank you


Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi BugmanJ ,

     

    I suggest you to copy M Query as below and paste it into Blank Query in Power Query Editor.

    let
    Source = Table.NestedJoin(#"Table 2", {"Key"}, #"Table 1", {"Key"}, "Table 1", JoinKind.LeftOuter),
    #"Expanded Table 1" = Table.ExpandTableColumn(Source, "Table 1", {"Key", "Name", "Date"}, {"Table 1.Key", "Table 1.Name", "Table 1.Date"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Expanded Table 1",{"Table 1.Key", "Table 1.Name", "Table 1.Date", "Key", "Payment"}),
    #"Added Conditional Column" = Table.AddColumn(#"Reordered Columns", "Table 1.Name1", each if [Table 1.Name] = null then "Unknown" else [Table 1.Name]),
    #"Reordered Columns1" = Table.ReorderColumns(#"Added Conditional Column",{"Table 1.Key", "Table 1.Name", "Table 1.Name1", "Table 1.Date", "Key", "Payment"}),
    #"Removed Columns" = Table.RemoveColumns(#"Reordered Columns1",{"Table 1.Name"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Table 1.Name1", "Table 1.Name"}, {"Key", "Table 2.Key"}, {"Payment", "Table 2.Payment"}})
in
    #"Renamed Columns"

    New Table:

     

    Best Regards,
    Rico Zhou

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi BugmanJ ,

     

    I suggest you to copy M Query as below and paste it into Blank Query in Power Query Editor.

    let
    Source = Table.NestedJoin(#"Table 2", {"Key"}, #"Table 1", {"Key"}, "Table 1", JoinKind.LeftOuter),
    #"Expanded Table 1" = Table.ExpandTableColumn(Source, "Table 1", {"Key", "Name", "Date"}, {"Table 1.Key", "Table 1.Name", "Table 1.Date"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Expanded Table 1",{"Table 1.Key", "Table 1.Name", "Table 1.Date", "Key", "Payment"}),
    #"Added Conditional Column" = Table.AddColumn(#"Reordered Columns", "Table 1.Name1", each if [Table 1.Name] = null then "Unknown" else [Table 1.Name]),
    #"Reordered Columns1" = Table.ReorderColumns(#"Added Conditional Column",{"Table 1.Key", "Table 1.Name", "Table 1.Name1", "Table 1.Date", "Key", "Payment"}),
    #"Removed Columns" = Table.RemoveColumns(#"Reordered Columns1",{"Table 1.Name"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Table 1.Name1", "Table 1.Name"}, {"Key", "Table 2.Key"}, {"Payment", "Table 2.Payment"}})
in
    #"Renamed Columns"

    New Table:

     

    Best Regards,
    Rico Zhou

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.