Search code examples
excellookupexcelquery

Excel, query: How to add specific info from query 2 into query 1 with lookup

I want to add data from the second query (Data) to the first query (Overview) depending on the information in the first query. I'll show my question with pictures to make it more clear. I have these two queries:

Overview

Data

I want to add the "ITEM" information from the Data query to overview query looking up the "NAME.1" Column. I think I'm close to the solution because I can lookup the row with this formula:

=Data{[NAME.1=[NAME.1]]}

Lookup

What do I need to add to the formula to only show the column "ITEM"? (and/or to show column number 0 in this case. Both solutions would be nice to understand it better how it works)

The data used in my screenshots:

Overview

NAME.1 NUMBER RANDOM
Name1 243324 qwsa
Name2 6747 dsfsdf
Name3 455 yyu
Name4 908098 hfhn
Name5 34 ertew
Name6 132 uil
Name7 64 tgvc
Name8 876 iorts

Data

ITEM Y/N NAME.1
123123 Y Name5
234324 Y Name1
345345 N Name7
456456 Y Name2
567567 N Name8
678678 N Name3
789789 Y Name6
890890 N Name4

Some extra information in reply to the answer of Darren below

When using merge, is it also possible to to merge the ITEM information if NAME.1=NAME.1 and if there is no NAME.1 match, to look if there is a match in NAME.2=NAME.2? Possibly with the result in the same column.

Overview

Overview2

NAME.1 NAME.2 NUMBER RANDOM
Name1 243324 qwsa
Name2 6747 dsfsdf
Name3 455 yyu
Name4 908098 hfhn
Name5 34 ertew
Name6 132 uil
Name7 64 tgvc
Name8 876 iorts

Data

Data2

ITEM Y/N NAME.1 NAME.2
123123 Y Name5
234324 Y Name1
345345 N Name7
456456 Y Name2
567567 N Name8
678678 N Name3
789789 Y Name6
890890 N Name4

Some extra information in reply to the answer of Ron below

When I use =try Data{[NAME.1=[NAME.1]]}[ITEM] otherwise Data{[NAME.2=[NAME.2]]}[ITEM]) and my tables look like this:

Overview

Overview3

NAME.1 NAME.2 NUMBER RANDOM
Name11 243324 qwsa
Name22 6747 dsfsdf
Name13 455 yyu
Name14 908098 hfhn
Name25 34 ertew
132 uil
64 tgvc
Name18 876 iorts

Data

Data3

ITEM Y/N NAME.1 NAME.2
123123 Y Name25
234324 Y Name11
345345 N Name17
456456 Y Name12 Name22
567567 N Name18
678678 N Name13
789789 Y Name16
890890 N Name14

Then I will get the wrong information in the table:

OverviewWrong

The green highlighted is correct, the 3 yellow ones are wrong and should be null. I think I should add some kind of if null=null give null or something in that direction?

Some extra information in reply to the suggestion of Ron below

When I do merge I get this code:

let
    Source = Excel.CurrentWorkbook(),
    #"Filtered Rows" = Table.SelectRows(Source, each ([Name] = "Table1")),
    #"Expanded Content" = Table.ExpandTableColumn(#"Filtered Rows", "Content", {"NAME", "NAME.2", "NUMBER", "RANDOM"}, {"NAME.1", "NAME.2", "NUMBER", "RANDOM"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Content", "AddInfoFromTable", each try Data{[NAME.1=[NAME.1]]}[ITEM] otherwise Data{[NAME.2=[NAME.2]]}[ITEM]),
    #"Merged Queries" = Table.NestedJoin(#"Added Custom", {"NAME.1"}, Data, {"NAME.1"}, "Data", JoinKind.LeftOuter),
    #"Expanded Data" = Table.ExpandTableColumn(#"Merged Queries", "Data", {"ITEM"}, {"ITEM"})
in
    #"Expanded Data"

And modify the code in "Advanced Editor" to this, it still shows no error

let
    Source = Excel.CurrentWorkbook(),
    #"Filtered Rows" = Table.SelectRows(Source, each ([Name] = "Table1")),
    #"Expanded Content" = Table.ExpandTableColumn(#"Filtered Rows", "Content", {"NAME", "NAME.2", "NUMBER", "RANDOM"}, {"NAME.1", "NAME.2", "NUMBER", "RANDOM"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Content", "AddInfoFromTable", each try Data{[NAME.1=[NAME.1]]}[ITEM] otherwise Data{[NAME.2=[NAME.2]]}[ITEM]),
    #"Merged Queries" = Table.NestedJoin(#"Added Custom", {"NAME.1","NAME.2"}, Data, {"NAME.1","NAME.2"}, "Data", JoinKind.LeftOuter),
    #"Expanded Data" = Table.ExpandTableColumn(#"Merged Queries", "Data", {"ITEM"}, {"ITEM"})
in
    #"Expanded Data"`

However, I'm not sure what to do with these values in your suggestions:

{"ITEM", each [ITEM]{0}, Int64.Type})
in
    Join

In my example the data in ITEM is numbers, in reality it contains a combination of numbers and letters (e.g., 0AB123 or AB123, etc.)

Solution

  • I believe the most efficient method is using the Table.Join function as demonstrated in another answer:

    let

//Read in the two tables
    SourceOverview = Excel.CurrentWorkbook(){[Name="Overview"]}[Content],
    Overview = Table.TransformColumnTypes(SourceOverview,{{"NAME", type text}, {"NUMBER", Int64.Type}, {"RANDOM", type text}}),

    SourceData = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    Data = Table.TransformColumnTypes(SourceData,{{"NAME", type text}, {"ITEM", Int64.Type}, {"Y/N", type text}}),

//Join the two tables using Name as common element
//and extract the ITEM column
    Joined = Table.TransformColumns(
        Table.NestedJoin(Overview,"NAME",Data,"NAME","ITEM", JoinKind.RightOuter),
        {"ITEM", each [ITEM]{0}, Int64.Type}),
    #"Sorted Rows" = Table.Sort(Joined,{{"NAME", Order.Ascending}})
    
in
    #"Sorted Rows"

    However, your method can also work the way you want by merely adding the Field Name to your formula:

    Data{[NAME=[NAME]]}[ITEM]

    Your posted data for this problem:
    Overview
    enter image description here

    Data
    enter image description here

    Results
    enter image description here

    Edit: Given your additional information of having to compare Name.1 or Name.2, you can modify the Table.Join function as follows:

    Please note that this method requires that NAME.1 or NAME.2 be null. If that is not the case, then coding would be more complex

       Join = Table.TransformColumns(    
        Table.NestedJoin(Overview,{"NAME.1","NAME.2"},Data2,{"NAME.1","NAME.2"},"ITEM",JoinKind.FullOuter),
        {"ITEM", each [ITEM]{0}, Int64.Type})
in
    Join

    Edit2: Now with further information that either table might or might not have Names in none, one or both NAME.1 and NAME.2 cells, the following shows two different techniques of managing that situation which probably gives the results you want

    let
    Source = Excel.CurrentWorkbook(){[Name="Overview3"]}[Content],
    #"Overview" = Table.TransformColumnTypes(Source,{
        {"NAME.1", type text}, {"NAME.2", type text}, {"NUMBER", Int64.Type}, {"RANDOM", type text}}),
  
//Add index column to be able to go back to original table order
    #"Added Index" = Table.AddIndexColumn(Overview, "Index", 0, 1, Int64.Type),

//Create a Name column that we will expand to all names in a single column
    #"Added Custom" = Table.AddColumn(#"Added Index", "Name", each List.RemoveNulls({[NAME.1]} & {[NAME.2]})),
    #"Expanded Name" = Table.ExpandListColumn(#"Added Custom", "Name"),

//Since Data table could also have Names in both NAME columns, we can unpivot (and delete the unneeded columns)
    #"Unpivot Data" = Table.UnpivotOtherColumns(Table.RemoveColumns(Data3,"Y/N"),{"ITEM"},"Attribute","Name"),
    #"Removed Columns" = Table.RemoveColumns(#"Unpivot Data",{"Attribute"}),

//Now we can merge the tables and return the ITEM
    #"Add Item" = Table.TransformColumns(
        Table.NestedJoin(#"Expanded Name","Name",#"Removed Columns","Name","ITEM",JoinKind.LeftOuter),
        {"ITEM", each try [ITEM]{0} otherwise null, type text}),
    #"Removed Columns1" = Table.RemoveColumns(#"Add Item",{"Name"}),

//Group by Index to return initial table in order
    #"Grouped Rows" = Table.Group(#"Removed Columns1", {"Index"}, {

    //If two names in the same row of Overview have different ITEMs, we will returned a concatenated string
        {"All", each  Table.Distinct(Table.TransformColumns(_, {"ITEM", (c)=>Text.Combine(_[ITEM], ", ")}))  , 
        type table [NAME.1=nullable text, NAME.2=nullable text, NUMBER=nullable number, RANDOM=nullable text, Index=number, ITEM=text]}}),


    #"Removed Columns2" = Table.RemoveColumns(#"Grouped Rows",{"Index"}),
    #"Expanded All" = Table.ExpandTableColumn(#"Removed Columns2", "All", {"NAME.1", "NAME.2", "NUMBER", "RANDOM", "Index", "ITEM"}),
    #"Removed Columns3" = Table.RemoveColumns(#"Expanded All",{"Index"})
in
    #"Removed Columns3"