Beginner Question - Merge Two Tables Without Nested Table

[GAWolfe]

I'm trying to merge/ relate two tables in power query. First table is sort of a beauty service booking record, which incluces client name, booking date, service item name and service item code. Second table, Item Cataogue" contains service item name, code, and other details about the service.

The data will later be used to create a dashboard and perhaps a copilot and canvas.

Is the best way to merge the two tables, or is there a way to observe relationship without joining?

And, when I try to join, the second table is nested. I want all the columns in [Item Catalogue] to be their individual columns. How do I do that?

[Anonymous]

When changing to Table.Join from Table. NestedJoin, you have to remove the "New Column Name" parameter--just delete what's in quotes before the JoinKind parameter. That's what caused your  Expression.Error: We cannot convert the value "Item Catalogue" to type Number.

There is nothing wrong with not wanting the nested tables--sometimes Table.Join is faster on well keyed and indexed tables, plus you can add the JoinAlgorithm parameter, which you can't use with Nested.Join.

--Nate

--Nate 

[Anonymous]

Hi @GAWolfe ,

I don't know if I truly understand your question. Maybe the question is really simple?

For example, after merging one table with another table. Click the button in the red box and select the column you want to expand.

In your side, you should click the button I circled to expand it.

Expand or aggregate the new merged table column

Best Regards,

Stephen Tao

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

[GAWolfe]

I used Table.Join instead and got this error: Expression.Error: We cannot convert the value "Item Catalogue" to type Number.

This is the code it generated. Item_Code is the common element. I tried not having the same column name in the 2 tables and the error still exists.

  #"Renamed columns" = Table.RenameColumns(#"Changed column type", {{"護理編號", "Item_Code"}}),

  #"Merged queries" = Table.Join(#"Renamed columns", {"Item_Code"}, #"Item Catalogue", {"Item_Code"}, "Item Catalogue", JoinKind.LeftOuter)

in

  #"Merged queries"

[ronrsnfld]

You won't be able to use Table.Join because, as I wrote, you have two columns with the same name.

Also, even if they had different names, your syntax is incorrect. It would be something like (with Item_Code renamed in Table 2 and assuming none of the other columns in Table 2 have the same name as in Table 1😞

#"Merged queries" = Table.Join(#"Renamed columns", {"Item_Code"}, #"Item Catalogue", {"Item_Code2"}, JoinKind.LeftOuter)

 What is your objection to nesting anyway?

[ronrsnfld]

Ensure your two tables do not have any columns with the same names. Then use Table.Join instead of Table.NestedJoin