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

Question

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:

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]]}

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

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

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

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

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:

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.)

Answer 1

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

Data

Results

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"

Answer 2

You should merge the two queries based on the Name.1 field.

On the Home tab select Merge Queries.
You'll get a dialog box with the the first table already selected. Select the second table in the lower half of the dialog box and then click the Name.1 header on each table.

Click OK and then expand your combined table.

Select which fields you want to show from the Data table and whether to include the original column name as a prefix and press OK.

The M code will look something like this:

let
    Source = Excel.CurrentWorkbook(){[Name="Overview"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"NAME.1", type text}, {"NUMBER", Int64.Type}, {"RANDOM", type any}, {"Name", type text}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"NAME.1"}, Data, {"NAME.1"}, "Data", JoinKind.LeftOuter),
    #"Expanded Data" = Table.ExpandTableColumn(#"Merged Queries", "Data", {"ITEM", "Y/N", "NAME.1", "Name"}, {"Data.ITEM", "Data.Y/N", "Data.NAME.1", "Data.Name"})
in
    #"Expanded Data"