Forum Discussion
I need help populating one table, based on another table.
I would like to populate one table based on entries in another table. The "Source" table has 4 columns. One "item" column and 3 "options" columns. When the user ticks a row in one of the option columns I would like the "item" in that same row to populate in another table. I know how to do this using the if function. The trouble is that there are blanks in the sheet. I would prefer the populated table to populate automatically without any blank spaces. Any suggestions?
I've attached the spreadsheet.
3 Replies
Hi Andrew,
Perhaps the easiest way is with Power Query
let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], ReplaceOneWithItem = Table.ReplaceValue(Source,1, each [Item],Replacer.ReplaceValue,{"Option 1", "Option 2", "Option 3"}), FilterEmptyRows = Table.SelectRows(ReplaceOneWithItem, each ([Option 1] <> null or [Option 2] <> null or [Option 3] <> null)), RemoveItems = Table.RemoveColumns(FilterEmptyRows,{"Item"}) in RemoveItemswith Refresh after changes
Thank you, this looks promising.
I have never used a power query, and used very little VBA. This seems like a viable solution but there are a few catches to workout. The query removes rows where the row = null, but this leaves blank cells in the results when the selection from the Source table is mismatched. I could not find a way to select individual cells and remove them based on their value. However, I did find that I could sort the table as a final step, which would filter the null results to the bottom. But the query will not let me sort each column individually. That is to say, once I sort by one column the rest of the columns become dependent on that initial sorting. And cannot be sorted separately. Well they can be, but there are still null cells left in between the secondarily sorted columns.
Another problem is that there may be more than one item selected per option. I assume this can be resolved by replacing the "1" in the step "ReplaceOneWithItem" with ">=1. Is it possible to loop this process and just add each queried column individually? Something like this:
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Removed Other Columns" = Table.SelectColumns(Source,{"Option 1", "Item"}),
ReplaceOneWithItem = Table.ReplaceValue(#"Removed Other Columns",<=1,
each [Item],Replacer.ReplaceValue,{"Option 1"}),
FilterEmptyRows = Table.SelectRows(ReplaceOneWithItem,
each ([Option 1] <> null)),
RemoveItems = Table.RemoveColumns(FilterEmptyRows,{"Item"})
in
RemoveItemsSource = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Removed Other Columns" = Table.SelectColumns(Source,{"Option 2", "Item"}),
ReplaceOneWithItem = Table.ReplaceValue(#"Removed Other Columns",<=1,
each [Item],Replacer.ReplaceValue,{"Option 2"}),
FilterEmptyRows = Table.SelectRows(ReplaceOneWithItem,
each ([Option 2] <> null)),
RemoveItems = Table.RemoveColumns(FilterEmptyRows,{"Item"})
in
RemoveItemsSource = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Removed Other Columns" = Table.SelectColumns(Source,{"Option 3", "Item"}),
ReplaceOneWithItem = Table.ReplaceValue(#"Removed Other Columns",<=1,
each [Item],Replacer.ReplaceValue,{"Option 3"}),
FilterEmptyRows = Table.SelectRows(ReplaceOneWithItem,
each ([Option 3] <> null)),
RemoveItems = Table.RemoveColumns(FilterEmptyRows,{"Item"})
in
RemoveItemsYour help is greatly appreciated BTW
Hi Andrew,
Will be glad to help, but not sure I understood your logic. Could you please manually create desirable configuration tables(s) for your Source table?
