Can you filter by the cell name in power query?

[Anonymous]

Hi guys,

can you filter by cell name in power query, for example A2? In my case I have a table with measured values, in one place a measured value is taken twice at different times, so you can't distinguish them only by the text. But the cell name was changed to C1f1 and C1f1_bi so that you can distinguish between the two. Now I wonder if I can filter by this name (unchanged would be A2 or A4). This is also a safer process because the cell name is not changed by accident. 
greetings Luke

[v-xuding-msft · Accepted solution]

Hi @Anonymous ，

As Jimmy said, it is not supported for Power Query to filter by cell names. We can try to filter columns like below:

= Table.SelectRows(#"Changed Type", each Text.Contains([Name], "A"))

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTI0UIrViVZyAjFNwUxnENMYzHQBMc3BTFcg08gQzHQDMU3ATLAJZkqxsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, Values = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Values", Int64.Type}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each Text.Contains([Name], "A"))
in
    #"Filtered Rows"

[Jimmy801]

Hello @Anonymous 

Power query is no spreadsheet :), it's working like a database. You can however reference columns and rows.

Check out this example... the {0} in my code is the name of the first column, the {1} is the second row.

After extracting the data of this "cell" you could use it as filter criteria on another step or another query

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WUtJRcjIEElAUqxOt5GiE4IOEYgE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [A = _t, B = _t, C = _t, D = _t, E = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"A", type text}, {"B", type text}, {"C", type text}, {"D", type text}, {"E", type text}}),
    ValueA2 = Table.Column(Source,Table.ColumnNames(Source){0}){1}
in
    ValueA2

 Copy paste this code to the advanced editor in a new blank query to see how the solution works.

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy