Forum Discussion
Search for a text in a table structured column and get column name in a added column
Hello,
I am desperately looking for a way to add a column which contains the columnname of a specific text that I am looking for in a nested table/structuded table ?
Here is the example (word to be searched is "Description")
The one able to help me will get my internal gratitude and respect ^^
Thank you so much in advance
3 Replies
- MAwwad
Solution Sage
You can create a new calculated column in your table and use the DAX function "CONTAINSROW" to check if the "Description" column contains the text you're looking for. If it does, you can use the DAX function "SELECTEDVALUE" to return the name of the column as a string. Here's an example of the DAX formula you can use:
Column Name = VAR DescriptionColumns = {"Description", "Long Description", "Short Description"} VAR Matches = FILTER( DescriptionColumns, CONTAINSROW(Table1, [Value], [Column Name], [Current Row Value], [Value] = "Description") ) RETURN IF(COUNTROWS(Matches) > 0, SELECTEDVALUE(Matches), BLANK())In this example, "Table1" is the name of your table, "[Value]" is the name of the column that contains the nested table, "[Column Name]" is the name of the column that you want to return in the new calculated column, and "[Current Row Value]" is the name of the column that contains the current row value. The formula creates a variable "DescriptionColumns" that contains the names of the columns you want to search for the text "Description". It then uses the "CONTAINSROW" function to filter the columns based on whether they contain the text "Description". Finally, it uses the "SELECTEDVALUE" function to return the name of the matching column.
Hi MAwwad ,
Thanks for your reply.
Yet, I actually need it in Power Query. I think I missed a bit of context in order to fully understand.
I am building a tool which reads PDF. However, Descreption that I need to grab are sometimes in column2 or in columns 3 (with several pages, and several pdf). Therefore I would need to know the emplacement before expanding the content of Data column. Therefore I really need to work on the embedded structured table before expanding it.
Any other ideas?
Thank you in advance 🙂
