Explore and share Fabric Notebooks to boost Power BI insights in the new community notebooks gallery.
Check it out now!Microsoft is giving away 50,000 FREE Microsoft Certification exam vouchers. Get Fabric certified for FREE! Learn more
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
Solved! Go to 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"
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"
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