Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi Experts,
I'm new to powerBi, well, my scenario,
I'm running a SQL Server DB with many tables, I know I can filter the tables under "Navigator" after I get the DB connected, however, it is very lagging in searching and dragging up and down (due to huge number of tables?).
How may I filter out unnessary tables/only take the desired tables during connecting to the DB?
I wrote the below statements under the "Advanced options -> SQL Statement (optional)"
First Try:
SELECT * FROM INFORMATION_SCHEMA.TABLES
where table_name like 'ABC%'
order by table_name
Second Try:
SELECT
FROM sys.tables AS t
INNER JOIN sys.schemas AS s
ON t.[schema_id] = s.[schema_id]
WHERE t.name like 'ABC%'
order by name
It filtered but the subsequent output is not that "Navigator", it is Not a list of filtered tables displaying in "Navigator",
how/what may I actually have in "SQL Statement (optional)" or any other way?
TQ
Solved! Go to Solution.
@Eric_Zhang wrote:
As far as I know, you can't filter the tables during connecting to SQL Server. However you can narrow down the navigation by filling the database(optional) field and then filter tables in the navigation by its searching tool. When the SQL Statement(Optional) fileds is filled, it would go to the form displaying the SQL Statement querying result rather than the navigation.
The Table Navigator in above is a very user friendly Form and it is the one I normally use, besides that, it's "clean" to filter out the necessary ones from beginning if I already knew it upfront what are absolutely undesire for my certain reports, and so on.If selecting tables from the navigation lags and there may be more reports based on the same tables, you can save the pbix as template after selecting tables and reuse it.
TQ for answer on this @Eric_Zhang (it's no then)
@ImkeF wrote:No, you shouldn't load the query. That way it only shows the metadata like it can be seen in your "Data-view".
Instead of loading, rightclick the query and reference it. This will create a new query where you can select your desired query from your now narrowed filtered pre-selection.
Don't write manual SQL-code at this moment, as this would break further query-folding.
TQ @ImkeF
After watching this video https://www.youtube.com/watch?v=ByIUx-HmQbw , well, it is a combination to achieve chart reports,
okay, for sharing purpose, let me complete the entire steps:
1. Get Data -> SQL Server
2. Put in Server Name & Database Name, and click OK
3. Right click on the Database, and click Edit
4. Make "Reference" (in the most left-hand side panel right click on the source and click Reference)
5. In the newly created reference, right click on any row (tables), and point to "Text Filters" (Begins With, Equals, etc), do filtering accordingly (optional step)
6. On your desire row (in this case, the row is a Table), click the gold color "Table" under column: "Data"
7. click "Close & Apply" to load the data (repeat step 4 - 6 before execute this for more one tables)
8. Data loaded, apply in your criterias. Done.
TQ all
Well, further explanation of my question regarding the how/what may I actually use "Advanced options -> SQL Statement (optional)" to achieve table filtering in a DB, it only shows the filtered Tables in the following "Navigator":
I think it is possible, right? Unless the underlying code writes as:
IF "SQL Statement (optional)" is presented THEN
GO TO other Form (instead of the above "Navigator")
The Table Navigator in above is a very user friendly Form and it is the one I normally use,
besides that, it's "clean" to filter out the necessary ones from beginning if I already knew it upfront what are absolutely undesire for my certain reports, and so on.
Anybody help? TQ
In the navigation-pane instead of select a query, just selct the database. After that a table-view of all your tables will be shown where you can apply your text-filters. Apply your filter and keep this query as your "menu-query". Reference all further queries to this one as a start. So you don't have to repeat theses steps for every further query:
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
TQ vm for this new idea to me, ImkeF.
Okay, I use the "begin with", after I set the "Text filters" accordingly, I should press "Close & Apply" menu, right? I did that then following it loaded in data:
However, in the "Fields", it shows Data, Item, Kind ...... instead of selected tables that I normally did and have:
Is that the right way/result? Sorry, I don't get what do you mean the "... as your "menu-query". Reference all further queries .......". Would you please share further? TQvm
No, you shouldn't load the query. That way it only shows the metadata like it can be seen in your "Data-view".
Instead of loading, rightclick the query and reference it. This will create a new query where you can select your desired query from your now narrowed filtered pre-selection.
Don't write manual SQL-code at this moment, as this would break further query-folding.
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
Were you talking about power bi desktop? I can't find the table view when click on the database.
As far as I know, you can't filter the tables during connecting to SQL Server. However you can narrow down the navigation by filling the database(optional) field and then filter tables in the navigation by its searching tool. When the SQL Statement(Optional) fileds is filled, it would go to the form displaying the SQL Statement querying result rather than the navigation.
The Table Navigator in above is a very user friendly Form and it is the one I normally use, besides that, it's "clean" to filter out the necessary ones from beginning if I already knew it upfront what are absolutely undesire for my certain reports, and so on.If selecting tables from the navigation lags and there may be more reports based on the same tables, you can save the pbix as template after selecting tables and reuse it.
@Eric_Zhang wrote:
As far as I know, you can't filter the tables during connecting to SQL Server. However you can narrow down the navigation by filling the database(optional) field and then filter tables in the navigation by its searching tool. When the SQL Statement(Optional) fileds is filled, it would go to the form displaying the SQL Statement querying result rather than the navigation.
The Table Navigator in above is a very user friendly Form and it is the one I normally use, besides that, it's "clean" to filter out the necessary ones from beginning if I already knew it upfront what are absolutely undesire for my certain reports, and so on.If selecting tables from the navigation lags and there may be more reports based on the same tables, you can save the pbix as template after selecting tables and reuse it.
TQ for answer on this @Eric_Zhang (it's no then)
@ImkeF wrote:No, you shouldn't load the query. That way it only shows the metadata like it can be seen in your "Data-view".
Instead of loading, rightclick the query and reference it. This will create a new query where you can select your desired query from your now narrowed filtered pre-selection.
Don't write manual SQL-code at this moment, as this would break further query-folding.
TQ @ImkeF
After watching this video https://www.youtube.com/watch?v=ByIUx-HmQbw , well, it is a combination to achieve chart reports,
okay, for sharing purpose, let me complete the entire steps:
1. Get Data -> SQL Server
2. Put in Server Name & Database Name, and click OK
3. Right click on the Database, and click Edit
4. Make "Reference" (in the most left-hand side panel right click on the source and click Reference)
5. In the newly created reference, right click on any row (tables), and point to "Text Filters" (Begins With, Equals, etc), do filtering accordingly (optional step)
6. On your desire row (in this case, the row is a Table), click the gold color "Table" under column: "Data"
7. click "Close & Apply" to load the data (repeat step 4 - 6 before execute this for more one tables)
8. Data loaded, apply in your criterias. Done.
TQ all
@Eric_Zhang: Yes, defnitely desktop.
Have a look at my screenshot above: In the foreground you see where to select your database and in the background you see the result that's been shown (with the same column names that have been loaded to Issmails table in the data view).
Or here step-by-step:
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
Thanks for your step-by-step instruction!
May I know what is the version of your power bi desktop? I've got the search textbox(I don't see it in your previous reply) and the buttons are greyed out when no any specific tables are selected.
Don't know where I left my head today...: But this was all in Excel. So sorry for waisting your time!
So in order to achieve the same result in PBI desktop, you first need to select one table - doesn't matter which one and click Edit.
Then you can either:
1) Go back to step "Source" and click on "Table" in the column "Data" next to your desired database. This will overwrite the existing steps by expanding the desired table. Or:
2) Edit the code in the advanced editor like this:
let Source= Sql.Databases("srvsql2"), AdventureWorksDW2012 = Source{[Name="AdventureWorksDW2012"]}[Data] // dbo_vDMPrep = AdventureWorksDW2012{[Schema="dbo",Item="vDMPrep"]}[Data] in // dbo_vDMPrep AdventureWorksDW2012
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
If you download the latest version (5/29) there should be a HierarchicalNavigation option that could really help you for this case. To activate it, you can enable "Navigate using full hierarchy" in the SQL Server dialog (under Advanced Options).
That way, you can drill into the schema you're interested in.
Although, looks like you might only have the 'dbo' schema, so this won't narrow down the results that much 😕
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
99 | |
80 | |
70 | |
59 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |