Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

Can you filter by the cell name in power query?

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

1 ACCEPTED SOLUTION
v-xuding-msft
Community Support
Community Support

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"

 

v-xuding-msft_0-1601617695226.pngv-xuding-msft_1-1601617709425.png

 

Best Regards,
Xue Ding
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

2 REPLIES 2
v-xuding-msft
Community Support
Community Support

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"

 

v-xuding-msft_0-1601617695226.pngv-xuding-msft_1-1601617709425.png

 

Best Regards,
Xue Ding
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Jimmy801
Community Champion
Community Champion

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

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors