How can I find number across the all table?

[Kopec]

Hi,

I need to find specific number in the table.

But I don´t know which column it will be located (I don´t know the Columns names too).

For the text I´m using the Table.FindText formula.

Is´t exist anything for the number??

Many thanks,

Honza

[Greg_Deckler]

@Kopec Difficult to say exactly, maybe try unpivoting your columns?

Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

[Kopec]

Hi,
@Greg_Deckler thank you for the links - it will be useful in the future, I'm sure.

I'm working on this: I have approx. 50 access dbs, each with several tables with different names, different structures, and different column names. 

I'm searching in these various databases and various tables many times for various value (and its particular  row). 

I wrote a searching machine (with parametr input from excel Cells) in PowerQuery, it works good when the searching value is formated as text in the database table, but somethimes I search for the value which is not formated as text in the database - it's formated as decimal number. And I can't change the data type for Column because I don´t know position and Column name (maybe it's possible but I don't know how to do it), and in the next searching cycle it can be in other DB with different structure).
I need to search for number across the all table withnout Column name specification.
See example below: 

[Icey · Accepted solution]

Hi @Kopec ,

How about transforming all columns types to text and find the corresponding rows. Finally, detect all columns' data types.

Details:

1. Suppose your original table looks like this:

ID	Type	Value
1	A	100
2	B	200
3	C	300

2. Then you can create a blank query and put the code below into advance editor.

let
    Source = Table.FindText(Table.TransformColumnTypes(#"Table",List.Transform(Table.ColumnNames(#"Table"), each {_, type text})),"100")
in
    Source

3. Then you will get this:

ID	Type	Value
1	A	100

4. Finally, you can select one column and click "Ctrl+A" to select all column. Then use "Detect data type" to  detect all columns' data types like so:

Best Regards,

Icey

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

[Kopec]

Hi, 

good idea, it works great when I should have the same column still. But I have cca 50 dababases with various count and name of columns.