Append list of values from another table

[CadenChow]

Hi all, may I get some help of the challenge below? The key is how can I read all values from other table column and put to a fields in another table? Thanks.

1. I have a table with list of country and a product table with location

2. I would like to create a new column with logic:

if product[location] is null then Location = AU, SG, JP, HK

[AllisonKennedy · Accepted solution]

@CadenChow That will make it difficult to filter by location, but you can do it: 

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WUtJRSiwoyElVitWJVvLwBnKTEvOAEMz3CgDyC1ITkzOUYmMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Location = _t, Fruit = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Location", type text}, {"Fruit", type text}}),
#"Added Conditional Column" = Table.AddColumn(#"Changed Type", "Location Option B", each if [Location] = "" then Text.Combine(AllLocationsList, ", ") else [Location])
in
#"Added Conditional Column"

Where you must have a list of locations also available in Power Query. See the FruitsByLocation query in the attached file (below signature)

[AllisonKennedy]

@CadenChow  Can you clarify your desired result please? It looks like you want to expand out the location column so in your example above you would end up with 4 rows for apple and 1 banana and 1 peach?

You need to use List functions, or you can create a mapping table: 

Old Location	New Location
SG	SG
JP	JP
HK	HK
AU	AU
	SG
	JP
	HK
	AU

and merge this mapping table into the existing table using Location and Old location as the matching key columns. Then click the double arrow on the new column to expand the New Location column

[CadenChow]

Thanks for your prompt reply. My desire result is to put all values in the location column to the null field.

[AllisonKennedy · Accepted solution]

@CadenChow That will make it difficult to filter by location, but you can do it: 

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WUtJRSiwoyElVitWJVvLwBnKTEvOAEMz3CgDyC1ITkzOUYmMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Location = _t, Fruit = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Location", type text}, {"Fruit", type text}}),
#"Added Conditional Column" = Table.AddColumn(#"Changed Type", "Location Option B", each if [Location] = "" then Text.Combine(AllLocationsList, ", ") else [Location])
in
#"Added Conditional Column"

Where you must have a list of locations also available in Power Query. See the FruitsByLocation query in the attached file (below signature)

[CadenChow]

I have tried to reproduce the solution but no luck....

When I tried to merge the tables they didn't map with all location but show null. Would you mind share more hints for that? Thanks.

[Syndicate_Admin]

@CadenChow choice are you trying to make? If you want option B, convert the locations to list (there is a button on the Transform tab), name it 'AllLocationsList', and then in the table add a custom column and place it in formula:

each if [Location] ? "" then Text.Combine(AllLocationsList, ", ") else [Location]

If you are trying a join, you need two columns in the location table.

[Syndicate_Admin]

@CadenChow choice are you trying to make? If you want option B, convert the locations to list (there is a button on the Transform tab), name it 'AllLocationsList', and then in the table add a custom column and place it in formula:

each if [Location] ? "" then Text.Combine(AllLocationsList, ", ") else [Location]

If you are trying a join, you need two columns in the location table.

[Syndicate_Admin]

@CadenChow choice are you trying to make? If you want option B, convert the locations to list (there is a button on the Transform tab), name it 'AllLocationsList', and then in the table add a custom column and place it in formula:

each if [Location] ? "" then Text.Combine(AllLocationsList, ", ") else [Location]

If you are trying a join, you need two columns in the location table.

[Syndicate_Admin · Accepted solution]

@CadenChow choice are you trying to make? If you want option B, convert the locations to list (there is a button on the Transform tab), name it 'AllLocationsList', and then in the table add a custom column and place it in formula:

each if [Location] ? "" then Text.Combine(AllLocationsList, ", ") else [Location]

If you are trying a join, you need two columns in the location table.

[Syndicate_Admin]

@CadenChow choice are you trying to make? If you want option B, convert the locations to list (there is a button on the Transform tab), name it 'AllLocationsList', and then in the table add a custom column and place it in formula:

each if [Location] ? "" then Text.Combine(AllLocationsList, ", ") else [Location]

If you are trying a join, you need two columns in the location table.

[Syndicate_Admin]

@CadenChow choice are you trying to make? If you want option B, convert the locations to list (there is a button on the Transform tab), name it 'AllLocationsList', and then in the table add a custom column and place it in formula:

each if [Location] ? "" then Text.Combine(AllLocationsList, ", ") else [Location]

If you are trying a join, you need two columns in the location table.

[Syndicate_Admin]

@CadenChow choice are you trying to make? If you want option B, convert the locations to list (there is a button on the Transform tab), name it 'AllLocationsList', and then in the table add a custom column and place it in formula:

each if [Location] ? "" then Text.Combine(AllLocationsList, ", ") else [Location]

If you are trying a join, you need two columns in the location table.

[AllisonKennedy]

@CadenChow  which option are you trying to do? If you want Option B, then convert the Locations to list (there's a button in Transform tab), name it 'AllLocationsList' and then in your table add a custom column and put this in formula: 

each if [Location] = "" then Text.Combine(AllLocationsList, ", ") else [Location]

If you're trying a merge, you need two columns in the location table.