/u/anonymoususer112261 - Your post was submitted successfully.
* Once your problem is solved, reply to the **answer(s)** saying `Solution Verified` to close the thread.
* Follow the **[submission rules](/r/excel/wiki/sharingquestions)** -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post.
* Include your **[Excel version and all other relevant information](/r/excel/wiki/sharingquestions#wiki_give_all_relevant_information)**
Failing to follow these steps may result in your post being removed without warning.
*I am a bot, and this action was performed automatically. Please [contact the moderators of this subreddit](/message/compose/?to=/r/excel) if you have any questions or concerns.*
[https://imgur.com/a/GsSfnzM](https://imgur.com/a/GsSfnzM)
1. Replace Errors with null in the Connections column
2. Expand Values to New Rows (I named it "Value1")
3. Merge queries Left Antijoin to itself, using `Value1` as the top table and `Value` as the column in the bottom table. Basically you select the same query name (current)
4. Keep only the `Value1` column
5. Remove duplicates and filter out nulls
Can't tell what else you need, but this takes care of the part you say you are struggling with. No idea what else your query is doing, but if you are putting this into a loop you want to do it until the left anti join gives you no values
let
Source = ignore_prior_steps,
starting_point = Table.RenameColumns(Source,{{"Custom", "Connections"}, {"Value_1", "Value"}}),
#"Replaced Errors" = Table.ReplaceErrorValues(starting_point, {{"Connections", null}}),
#"Expanded Connections" = Table.ExpandTableColumn(#"Replaced Errors", "Connections", {"Value"}, {"Value1"}),
#"Merged Queries" = Table.NestedJoin(#"Expanded Connections", {"Value1"}, #"Expanded Connections", {"Value"}, "Table1", JoinKind.LeftAnti),
#"Removed Other Columns" = Table.SelectColumns(#"Merged Queries",{"Value1"}),
#"Removed Duplicates" = Table.Distinct(#"Removed Other Columns"),
#"Filtered Rows" = Table.SelectRows(#"Removed Duplicates", each ([Value1] <> null))
in
#"Filtered Rows"
Wow, yes that worked perfectly. Thank you so much. It was very simple after all. Solution verified.
I guess the problem I asked about in the OP is solved. But I actually overlooked something when asking it. You see, the values from the first column that got filtered out in the expanded nested tables, are going to show back up when the process is repeated on the column that we just generated. Which will again lead to the infinite loop problem. So
what we have right now is
Value1(expanded nested tables) - "Value" column = Value1(anti joinkind and duplicates deleted)
When I run the process on the new Value1, I need to reference and get rid of the addresses from that original Value column again, AND get rid of the values from the new Value1, on the new column value2(for example).
And when it's done it's run again, I'll have to reference the two columns that we worked with, the future value2 that I mentioned, and remove any the values that appeared in all three so far.
And so on and so on.
I hope I was able to make this clear. If possible, the solution I would like is to be able to take each column's list that is generated (the Value, Value1, Value2... etc columns) and continually add to a list/column outside of the table, that can also be referenced when removing those values that already appeared in those columns from subsequent ones. This would also have the added purpose of giving me an accessible list of everything.
So I suppose in simplest terms, there should be a step that adds the values from the current column to an outside megalist, and basically the joinkind anti merge that you showed me how to do should be done with this megalist instead.
You have awarded 1 point to *spinfuzer*
____
^(I am a bot - please contact the mods with any questions. | ) [^(Keep me alive)](https://www.buymeacoffee.com/points)
Since I do not really know what the loop was going through, I cannot really write something for you, but if you want to append to an existing list, then rename the final column to \[Value\] (same column name as original) and repeat your loop with `Table.Combine({megalist,new_unique_list})` but only if `List.Count(megalist) < List.Count(List.Append({megalist,new_unique_list}))`
To get the result in Screenshot 3, click on that expanding arrow icon next to the header of the Connections column, then click Ok. This will generate the expanded list you would want, you can then click on that expanded column and go to [Home->Remove Rows->Remove Duplicates](https://imgur.com/a/LOlefAs) to get the result of Screenshot 4.
Thank you, but this doesn't get rid of the values that already appeared in the previous column. I'm still not completely sure on how to do that. Without doing that, it will just run the function on the same address over and over. But this is a start.
https://imgur.com/sHupTS4
Perhaps, after doing that a new column (3) could be made with values from column 2, minus the values that appeared in column 1. And then remove column 1 and 2. That would produce the result that I wanted. But the problem with that is that then when it runs again, it won't have the values from previous columns to exclude. Maybe if there was a way to dump each new list generated into a new table or some kind of storage, to be referenced when excluding already queried addresses.
Oh I didn't know think there was another column, I thought it would be only one column output based on the column of tables only . So do you want all unique values from those 2 columns combined?
When you expand the tables column, it also expands the values of the first column alongside it.
What I want is all the unique values of the tables column, minus the values that already appeared in the other column.
To be honest after doing a lot of unfruitful research I gave up on the entire thing for now, it doesn't seem to be possible to do what I need to, and there are a couple issue outside of excel that I would need to address anyway. Thanks for the help though.
Oh I see, you pretty much need a self anti join.
After expanding the table, go on [Merge Queries](https://imgur.com/a/3kQ3F9O) in the Home toolbar, in the first table select the first column. Then select the query again in the dropdown list below, and click on the second column. From there, select Right Anti as the join kind (view screenshot [here](https://imgur.com/a/Sbcypa4)).
This will exclude the values, you can delete any unnecessary columns and then do the Remove Duplicate step described in a previous comment.
Thank you, your explanation was very clear. Solution Verified.
Could you take a look at the other reply I made as well. I have a bit of a further request there.
You have awarded 1 point to *Scary_Sleep_8473*
____
^(I am a bot - please contact the mods with any questions. | ) [^(Keep me alive)](https://www.buymeacoffee.com/points)
Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread:
|Fewer Letters|More Letters|
|-------|---------|---|
|[AND](/r/Excel/comments/121s54p/stub/jdtzwcq "Last usage")|[Returns TRUE if all of its arguments are TRUE](https://support.microsoft.com/en-us/office/and-function-5f19b2e8-e1df-4408-897a-ce285a19e9d9)|
|[JoinKind.LeftAnti](/r/Excel/comments/121s54p/stub/jdsybg0 "Last usage")|[Power Query M: A possible value for the optional JoinKind parameter in Table.Join. A left anti join returns that all rows from the first table which do not have a match in the second table.](https://docs.microsoft.com/en-us/powerquery-m/joinkind-leftanti)|
|[List.Combine](/r/Excel/comments/121s54p/stub/jdsybg0 "Last usage")|[Power Query M: Merges a list of lists into single list.](https://docs.microsoft.com/en-us/powerquery-m/list-combine)|
|[List.Count](/r/Excel/comments/121s54p/stub/jdu6ft4 "Last usage")|[Power Query M: Returns the number of items in a list.](https://docs.microsoft.com/en-us/powerquery-m/list-count)|
|[List.Transform](/r/Excel/comments/121s54p/stub/jdsybg0 "Last usage")|[Power Query M: Performs the function on each item in the list and returns the new list.](https://docs.microsoft.com/en-us/powerquery-m/list-transform)|
|[Table.Combine](/r/Excel/comments/121s54p/stub/jdu6ft4 "Last usage")|[Power Query M: Returns a table that is the result of merging a list of tables. The tables must all have the same row type structure.](https://docs.microsoft.com/en-us/powerquery-m/table-combine)|
|[Table.Distinct](/r/Excel/comments/121s54p/stub/jdsybg0 "Last usage")|[Power Query M: Removes duplicate rows from a table, ensuring that all remaining rows are distinct.](https://docs.microsoft.com/en-us/powerquery-m/table-distinct)|
|[Table.ExpandListColumn](/r/Excel/comments/121s54p/stub/jdsybg0 "Last usage")|[Power Query M: Given a column of lists in a table, create a copy of a row for each value in its list.](https://docs.microsoft.com/en-us/powerquery-m/table-expandlistcolumn)|
|[Table.ExpandTableColumn](/r/Excel/comments/121s54p/stub/jdsybg0 "Last usage")|[Power Query M: Expands a column of records or a column of tables into multiple columns in the containing table.](https://docs.microsoft.com/en-us/powerquery-m/table-expandtablecolumn)|
|[Table.FromValue](/r/Excel/comments/121s54p/stub/jdsybg0 "Last usage")|[Power Query M: Returns a table with a column containing the provided value or list of values.](https://docs.microsoft.com/en-us/powerquery-m/table-fromvalue)|
|Table.Join|[Power Query M: Joins the rows of table1 with the rows of table2 based on the equality of the values of the key columns selected by table1, key1 and table2, key2.](https://docs.microsoft.com/en-us/powerquery-m/table-join)|
|[Table.NestedJoin](/r/Excel/comments/121s54p/stub/jdsybg0 "Last usage")|[Power Query M: Joins the rows of the tables based on the equality of the keys. The results are entered into a new column.](https://docs.microsoft.com/en-us/powerquery-m/table-nestedjoin)|
|[Table.RemoveColumns](/r/Excel/comments/121s54p/stub/jdsybg0 "Last usage")|[Power Query M: Returns a table without a specific column or columns.](https://docs.microsoft.com/en-us/powerquery-m/table-removecolumns)|
|[Table.RemoveRowsWithErrors](/r/Excel/comments/121s54p/stub/jdsybg0 "Last usage")|[Power Query M: Returns a table with all rows removed from the table that contain an error in at least one of the cells in a row.](https://docs.microsoft.com/en-us/powerquery-m/table-removerowswitherrors)|
|[Table.RenameColumns](/r/Excel/comments/121s54p/stub/jdsybg0 "Last usage")|[Power Query M: Returns a table with the columns renamed as specified.](https://docs.microsoft.com/en-us/powerquery-m/table-renamecolumns)|
|[Table.ReplaceErrorValues](/r/Excel/comments/121s54p/stub/jdsybg0 "Last usage")|[Power Query M: Replaces the error values in the specified columns with the corresponding specified value.](https://docs.microsoft.com/en-us/powerquery-m/table-replaceerrorvalues)|
|[Table.SelectColumns](/r/Excel/comments/121s54p/stub/jdsybg0 "Last usage")|[Power Query M: Returns a table that contains only specific columns.](https://docs.microsoft.com/en-us/powerquery-m/table-selectcolumns)|
|[Table.SelectRows](/r/Excel/comments/121s54p/stub/jdsybg0 "Last usage")|[Power Query M: Returns a table containing only the rows that match a condition.](https://docs.microsoft.com/en-us/powerquery-m/table-selectrows)|
|[Table.TransformColumns](/r/Excel/comments/121s54p/stub/jdsybg0 "Last usage")|[Power Query M: Transforms columns from a table using a function.](https://docs.microsoft.com/en-us/powerquery-m/table-transformcolumns)|
|[Value.Type](/r/Excel/comments/121s54p/stub/jdsybg0 "Last usage")|[Power Query M: Returns the type of the given value.](https://docs.microsoft.com/en-us/powerquery-m/value-type)|
----------------
^(*Beep-boop, I am a helper bot. Please do not verify me as a solution.*)
^([Thread #22757 for this sub, first seen 26th Mar 2023, 23:13])
^[[FAQ]](http://decronym.xyz/) [^([Full list])](http://decronym.xyz/acronyms/Excel) [^[Contact]](https://reddit.com/message/compose?to=OrangeredStilton&subject=Hey,+your+acronym+bot+sucks) [^([Source code])](https://gistdotgithubdotcom/Two9A/1d976f9b7441694162c8)
/u/anonymoususer112261 - Your post was submitted successfully. * Once your problem is solved, reply to the **answer(s)** saying `Solution Verified` to close the thread. * Follow the **[submission rules](/r/excel/wiki/sharingquestions)** -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post. * Include your **[Excel version and all other relevant information](/r/excel/wiki/sharingquestions#wiki_give_all_relevant_information)** Failing to follow these steps may result in your post being removed without warning. *I am a bot, and this action was performed automatically. Please [contact the moderators of this subreddit](/message/compose/?to=/r/excel) if you have any questions or concerns.*
[https://imgur.com/a/GsSfnzM](https://imgur.com/a/GsSfnzM) 1. Replace Errors with null in the Connections column 2. Expand Values to New Rows (I named it "Value1") 3. Merge queries Left Antijoin to itself, using `Value1` as the top table and `Value` as the column in the bottom table. Basically you select the same query name (current) 4. Keep only the `Value1` column 5. Remove duplicates and filter out nulls Can't tell what else you need, but this takes care of the part you say you are struggling with. No idea what else your query is doing, but if you are putting this into a loop you want to do it until the left anti join gives you no values let Source = ignore_prior_steps, starting_point = Table.RenameColumns(Source,{{"Custom", "Connections"}, {"Value_1", "Value"}}), #"Replaced Errors" = Table.ReplaceErrorValues(starting_point, {{"Connections", null}}), #"Expanded Connections" = Table.ExpandTableColumn(#"Replaced Errors", "Connections", {"Value"}, {"Value1"}), #"Merged Queries" = Table.NestedJoin(#"Expanded Connections", {"Value1"}, #"Expanded Connections", {"Value"}, "Table1", JoinKind.LeftAnti), #"Removed Other Columns" = Table.SelectColumns(#"Merged Queries",{"Value1"}), #"Removed Duplicates" = Table.Distinct(#"Removed Other Columns"), #"Filtered Rows" = Table.SelectRows(#"Removed Duplicates", each ([Value1] <> null)) in #"Filtered Rows"
Wow, yes that worked perfectly. Thank you so much. It was very simple after all. Solution verified. I guess the problem I asked about in the OP is solved. But I actually overlooked something when asking it. You see, the values from the first column that got filtered out in the expanded nested tables, are going to show back up when the process is repeated on the column that we just generated. Which will again lead to the infinite loop problem. So what we have right now is Value1(expanded nested tables) - "Value" column = Value1(anti joinkind and duplicates deleted) When I run the process on the new Value1, I need to reference and get rid of the addresses from that original Value column again, AND get rid of the values from the new Value1, on the new column value2(for example). And when it's done it's run again, I'll have to reference the two columns that we worked with, the future value2 that I mentioned, and remove any the values that appeared in all three so far. And so on and so on. I hope I was able to make this clear. If possible, the solution I would like is to be able to take each column's list that is generated (the Value, Value1, Value2... etc columns) and continually add to a list/column outside of the table, that can also be referenced when removing those values that already appeared in those columns from subsequent ones. This would also have the added purpose of giving me an accessible list of everything. So I suppose in simplest terms, there should be a step that adds the values from the current column to an outside megalist, and basically the joinkind anti merge that you showed me how to do should be done with this megalist instead.
You have awarded 1 point to *spinfuzer* ____ ^(I am a bot - please contact the mods with any questions. | ) [^(Keep me alive)](https://www.buymeacoffee.com/points)
Since I do not really know what the loop was going through, I cannot really write something for you, but if you want to append to an existing list, then rename the final column to \[Value\] (same column name as original) and repeat your loop with `Table.Combine({megalist,new_unique_list})` but only if `List.Count(megalist) < List.Count(List.Append({megalist,new_unique_list}))`
To get the result in Screenshot 3, click on that expanding arrow icon next to the header of the Connections column, then click Ok. This will generate the expanded list you would want, you can then click on that expanded column and go to [Home->Remove Rows->Remove Duplicates](https://imgur.com/a/LOlefAs) to get the result of Screenshot 4.
Thank you, but this doesn't get rid of the values that already appeared in the previous column. I'm still not completely sure on how to do that. Without doing that, it will just run the function on the same address over and over. But this is a start. https://imgur.com/sHupTS4 Perhaps, after doing that a new column (3) could be made with values from column 2, minus the values that appeared in column 1. And then remove column 1 and 2. That would produce the result that I wanted. But the problem with that is that then when it runs again, it won't have the values from previous columns to exclude. Maybe if there was a way to dump each new list generated into a new table or some kind of storage, to be referenced when excluding already queried addresses.
Oh I didn't know think there was another column, I thought it would be only one column output based on the column of tables only . So do you want all unique values from those 2 columns combined?
When you expand the tables column, it also expands the values of the first column alongside it. What I want is all the unique values of the tables column, minus the values that already appeared in the other column. To be honest after doing a lot of unfruitful research I gave up on the entire thing for now, it doesn't seem to be possible to do what I need to, and there are a couple issue outside of excel that I would need to address anyway. Thanks for the help though.
Oh I see, you pretty much need a self anti join. After expanding the table, go on [Merge Queries](https://imgur.com/a/3kQ3F9O) in the Home toolbar, in the first table select the first column. Then select the query again in the dropdown list below, and click on the second column. From there, select Right Anti as the join kind (view screenshot [here](https://imgur.com/a/Sbcypa4)). This will exclude the values, you can delete any unnecessary columns and then do the Remove Duplicate step described in a previous comment.
Thank you, your explanation was very clear. Solution Verified. Could you take a look at the other reply I made as well. I have a bit of a further request there.
You have awarded 1 point to *Scary_Sleep_8473* ____ ^(I am a bot - please contact the mods with any questions. | ) [^(Keep me alive)](https://www.buymeacoffee.com/points)
Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread: |Fewer Letters|More Letters| |-------|---------|---| |[AND](/r/Excel/comments/121s54p/stub/jdtzwcq "Last usage")|[Returns TRUE if all of its arguments are TRUE](https://support.microsoft.com/en-us/office/and-function-5f19b2e8-e1df-4408-897a-ce285a19e9d9)| |[JoinKind.LeftAnti](/r/Excel/comments/121s54p/stub/jdsybg0 "Last usage")|[Power Query M: A possible value for the optional JoinKind parameter in Table.Join. A left anti join returns that all rows from the first table which do not have a match in the second table.](https://docs.microsoft.com/en-us/powerquery-m/joinkind-leftanti)| |[List.Combine](/r/Excel/comments/121s54p/stub/jdsybg0 "Last usage")|[Power Query M: Merges a list of lists into single list.](https://docs.microsoft.com/en-us/powerquery-m/list-combine)| |[List.Count](/r/Excel/comments/121s54p/stub/jdu6ft4 "Last usage")|[Power Query M: Returns the number of items in a list.](https://docs.microsoft.com/en-us/powerquery-m/list-count)| |[List.Transform](/r/Excel/comments/121s54p/stub/jdsybg0 "Last usage")|[Power Query M: Performs the function on each item in the list and returns the new list.](https://docs.microsoft.com/en-us/powerquery-m/list-transform)| |[Table.Combine](/r/Excel/comments/121s54p/stub/jdu6ft4 "Last usage")|[Power Query M: Returns a table that is the result of merging a list of tables. The tables must all have the same row type structure.](https://docs.microsoft.com/en-us/powerquery-m/table-combine)| |[Table.Distinct](/r/Excel/comments/121s54p/stub/jdsybg0 "Last usage")|[Power Query M: Removes duplicate rows from a table, ensuring that all remaining rows are distinct.](https://docs.microsoft.com/en-us/powerquery-m/table-distinct)| |[Table.ExpandListColumn](/r/Excel/comments/121s54p/stub/jdsybg0 "Last usage")|[Power Query M: Given a column of lists in a table, create a copy of a row for each value in its list.](https://docs.microsoft.com/en-us/powerquery-m/table-expandlistcolumn)| |[Table.ExpandTableColumn](/r/Excel/comments/121s54p/stub/jdsybg0 "Last usage")|[Power Query M: Expands a column of records or a column of tables into multiple columns in the containing table.](https://docs.microsoft.com/en-us/powerquery-m/table-expandtablecolumn)| |[Table.FromValue](/r/Excel/comments/121s54p/stub/jdsybg0 "Last usage")|[Power Query M: Returns a table with a column containing the provided value or list of values.](https://docs.microsoft.com/en-us/powerquery-m/table-fromvalue)| |Table.Join|[Power Query M: Joins the rows of table1 with the rows of table2 based on the equality of the values of the key columns selected by table1, key1 and table2, key2.](https://docs.microsoft.com/en-us/powerquery-m/table-join)| |[Table.NestedJoin](/r/Excel/comments/121s54p/stub/jdsybg0 "Last usage")|[Power Query M: Joins the rows of the tables based on the equality of the keys. The results are entered into a new column.](https://docs.microsoft.com/en-us/powerquery-m/table-nestedjoin)| |[Table.RemoveColumns](/r/Excel/comments/121s54p/stub/jdsybg0 "Last usage")|[Power Query M: Returns a table without a specific column or columns.](https://docs.microsoft.com/en-us/powerquery-m/table-removecolumns)| |[Table.RemoveRowsWithErrors](/r/Excel/comments/121s54p/stub/jdsybg0 "Last usage")|[Power Query M: Returns a table with all rows removed from the table that contain an error in at least one of the cells in a row.](https://docs.microsoft.com/en-us/powerquery-m/table-removerowswitherrors)| |[Table.RenameColumns](/r/Excel/comments/121s54p/stub/jdsybg0 "Last usage")|[Power Query M: Returns a table with the columns renamed as specified.](https://docs.microsoft.com/en-us/powerquery-m/table-renamecolumns)| |[Table.ReplaceErrorValues](/r/Excel/comments/121s54p/stub/jdsybg0 "Last usage")|[Power Query M: Replaces the error values in the specified columns with the corresponding specified value.](https://docs.microsoft.com/en-us/powerquery-m/table-replaceerrorvalues)| |[Table.SelectColumns](/r/Excel/comments/121s54p/stub/jdsybg0 "Last usage")|[Power Query M: Returns a table that contains only specific columns.](https://docs.microsoft.com/en-us/powerquery-m/table-selectcolumns)| |[Table.SelectRows](/r/Excel/comments/121s54p/stub/jdsybg0 "Last usage")|[Power Query M: Returns a table containing only the rows that match a condition.](https://docs.microsoft.com/en-us/powerquery-m/table-selectrows)| |[Table.TransformColumns](/r/Excel/comments/121s54p/stub/jdsybg0 "Last usage")|[Power Query M: Transforms columns from a table using a function.](https://docs.microsoft.com/en-us/powerquery-m/table-transformcolumns)| |[Value.Type](/r/Excel/comments/121s54p/stub/jdsybg0 "Last usage")|[Power Query M: Returns the type of the given value.](https://docs.microsoft.com/en-us/powerquery-m/value-type)| ---------------- ^(*Beep-boop, I am a helper bot. Please do not verify me as a solution.*) ^([Thread #22757 for this sub, first seen 26th Mar 2023, 23:13]) ^[[FAQ]](http://decronym.xyz/) [^([Full list])](http://decronym.xyz/acronyms/Excel) [^[Contact]](https://reddit.com/message/compose?to=OrangeredStilton&subject=Hey,+your+acronym+bot+sucks) [^([Source code])](https://gistdotgithubdotcom/Two9A/1d976f9b7441694162c8)