none
Return a column's value after merge 2 table RRS feed

  • Question

  • Dear all,

    I'm kinda new to Power Query & i need some help on handle a null value after merge 2 table. i can easily done this in excel by Vlookup

    ex: =VLOOKUP([@[Final Agents]],Table12,2,0),[@[Final Agents]])

    This formular will return a value in Table12, if it does not find the match in Table12, it will return the look up value (in this case it is a value in column [Final Agents]

    The issue here is in Power Query,if it does not find the match in Table12, it returns a null value while i want it return a value in column [Final Agents].

    Can anyone give me any advise. 

    Thursday, January 23, 2020 3:01 PM

Answers

  • So you don't have to wait tomorrow...

    You provided a workbook with Tables "linked" to OneDrive so I had to do a copy/paste of the values to create Table1 and Table2, so before saying something like "this doesn't work", make sure you make the appropriate replacements below:

    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        ChangedTypes = Table.TransformColumnTypes(Source,{{"Index", Int64.Type}, {"Consignee Name", type text}}),
        JoinedTable2 = Table.NestedJoin(ChangedTypes,"Consignee Name", Table2,"Consignee", "Table2", JoinKind.LeftOuter),
        AddedCneeGroup = Table.AddColumn(JoinedTable2, "Cnee Group", each
            if Table.IsEmpty([Table2]) then [Consignee Name] else Table.First(Table2)[Cnee Group], type text
        ),
        RemovedTable2 = Table.RemoveColumns(AddedCneeGroup,{"Table2"})
    in
        RemovedTable2

    Thursday, January 23, 2020 5:39 PM

All replies

  • ex: =VLOOKUP([@[Final Agents]],Table12,2,0),[@[Final Agents]])

    This formular will return a value in Table12, if it does not find the match in Table12, it will return the look up value (in this case it is a value in column [Final Agents] 

    Hi

    Here your =VLOOKUP([@[Final Agents]],Table12,2,0),[@[Final Agents]]) can't even be validated (There's a problem with this formula...)

    So, suggestion is: show (if possible) a picture of your 1st Table, Table12, the expected result and the PQ code you've been using so far to merge your 2 tables. If you can't upload a picture, upload a sample of your workbook - still with the expected result - on a file sharing service (i.e. OneDrive) & post the hyperlink to the file in your next reply

    Thursday, January 23, 2020 3:38 PM
  • HI Lz,

    Pls refer below link to the worksheet.

    drive.google.com/open?id=1PcMNGSOXif_xzzWc6c_VEKyxjKPKWt1t

    Sheet 2 i used to take the 2nd column and put into Table 1

    Sheet1 is the result after merged from Table 2, as you can see indexed row 25 (PT. ERATEX DJAJA TBK) is not in the Table 2 so it return a null.

    i used the default merge query in power query, the formular as below. 

    = Table.NestedJoin(#"Reordered Columns", {"Consignee Name"}, #"Table 2", {"Consignee"}, "Table 2", JoinKind.LeftOuter)

    Thursday, January 23, 2020 4:40 PM
  • OK, but one information is still missing

    Taking your example of [Index]=25 in Table1, where [Consignee Name] does not match in Table2 [Consignee], which value from which Table do you want to see in column [Cnee Group] instead of null???

    Thursday, January 23, 2020 5:06 PM
  • Hi Lz,

    i want to return [Consignee Name]'s value if null, for example [Cnee Group]  will show PT. ERATEX DJAJA TBK for index row 25

    Thursday, January 23, 2020 5:16 PM
  • OK, I'll have a look at that tomorrow

    In the meantime - if this was me - I would look at data quality. Just saw in your Table2, row 12 a LineFeed after the value (FUNEUROP). If there is one like that maybe there are others...

    Thursday, January 23, 2020 5:21 PM
  • So you don't have to wait tomorrow...

    You provided a workbook with Tables "linked" to OneDrive so I had to do a copy/paste of the values to create Table1 and Table2, so before saying something like "this doesn't work", make sure you make the appropriate replacements below:

    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        ChangedTypes = Table.TransformColumnTypes(Source,{{"Index", Int64.Type}, {"Consignee Name", type text}}),
        JoinedTable2 = Table.NestedJoin(ChangedTypes,"Consignee Name", Table2,"Consignee", "Table2", JoinKind.LeftOuter),
        AddedCneeGroup = Table.AddColumn(JoinedTable2, "Cnee Group", each
            if Table.IsEmpty([Table2]) then [Consignee Name] else Table.First(Table2)[Cnee Group], type text
        ),
        RemovedTable2 = Table.RemoveColumns(AddedCneeGroup,{"Table2"})
    in
        RemovedTable2

    Thursday, January 23, 2020 5:39 PM