0

So here is how far I could get with this

let
    Source = Excel.Workbook(File.Contents("C:\Port Inventory.xlsx"), null, true),
    #"All in one_Sheet" = Source{[Item="All in one",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(#"All in one_Sheet", [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Switch", type text}, {"Port", type text}, {"Name", type text}, {"Column2", type text}, {"Column1", type any}, {"Status", type text}, {"Vlan", type any}, {"Duplex", type text}, {"Speed", type any}, {"Type", type text}, {"Description", type text}, {"Migrated", type text}}),
    #"Filtered Rows" = #!"Table.SelectRows(#""Changed Type"", each [Switch] = 'Migration sheet'!D2)"
in
    #"Filtered Rows"

I do not know ho to refer a cell of a sheet in the current workbook. I guess this [Switch] = 'Migration sheet'!D2 needs to the replaced with something else in the third last line

I want to use this query as a source for the data for a drop down list. let's say cell D2 contains the value I want to filter by. This will be used in a Power Query that will be used as List for the drown down options of a cell (E2) that will be adjacent to C2

MiniMe
  • 1,057
  • 4
  • 22
  • 47
  • Make it a named range – Ron Rosenfeld Mar 22 '21 at 18:55
  • Related: https://stackoverflow.com/questions/26989279 – Alexis Olson Mar 22 '21 at 20:47
  • this will be applied for all the cells in an column. I can't just name every cell in the adjacent column so I can use this solution. As far as I can understand the named range applies to a column so then how do I access a cell in a named range ? Using INDEX(RangeName , cell_index) ? Will have to test this – MiniMe Mar 23 '21 at 00:06
  • You asked how to refer to **a cell** in the current workbook in your Power Query. You can name a single cell. Not sure what you mean by ***this will be applied for all the cells in an column*** – Ron Rosenfeld Mar 23 '21 at 00:10
  • I did, hopping to get an answer like "use Migration sheet'!D2" or similar and and I hopes I could use a formula that will increment the D and the 2 in the above and then apply the same formula to all the below cells as you would usually do in situations like this (like in this video https://www.youtube.com/watch?v=YM87Aj764IY&ab_channel=XuCui) – MiniMe Mar 23 '21 at 00:48
  • So basically what I am after is to be able to write a value in a cell in column C and then have the adjacent D cell configured with a drop down list for values, where the values come from a query that selects all the rows from another sheet where the value in the C cell is present in a certain column – MiniMe Mar 23 '21 at 00:51

0 Answers0