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,
I am getting data through an ODBC Connection. The table I am trying to import has 30 million rows. The entire data can't be imported as it throws error.
I have a date column in the table. I am not able to apply filter on it as it is treated as text by Power Bi though it is a datetime datatype in the IBM DB2
If I convert it to date/time and then apply filter, it still get the entire data before applyng filter. I am not sure how to navigate this situation.
Appreciate the help
Solved! Go to Solution.
You can change the source from import without breaking you reports as long as the name and column list stay the same.
This is an example odbc source from the advanced editor
let Source = Odbc.DataSource("dsn=WideWorldImporters", [HierarchicalNavigation=true]), WideWorldImporters_Database = Source{[Name="WideWorldImporters",Kind="Database"]}[Data], Website_Schema = WideWorldImporters_Database{[Name="Website",Kind="Schema"]}[Data], Customers_View = Website_Schema{[Name="Customers",Kind="View"]}[Data] in Customers_View
You can change it manually in the advanced editor to something like this. Changing the query to limit to relevant rows.
let
Source = Odbc.Query("dsn=WideWorldImporters", "SELECT TOP (1000) [CustomerID]#(lf) ,[CustomerName]#(lf) ,[CustomerCategoryName]#(lf) ,[PrimaryContact]#(lf) ,[AlternateContact]#(lf) ,[PhoneNumber]#(lf) ,[FaxNumber]#(lf) ,[BuyingGroupName]#(lf) ,[WebsiteURL]#(lf) ,[DeliveryMethod]#(lf) ,[CityName]#(lf) ,[DeliveryLocation]#(lf) ,[DeliveryRun]#(lf) ,[RunPosition]#(lf) FROM [WideWorldImporters].[Website].[Customers]#(lf) order by [CustomerID] desc")
in
Source
You can click the settings cog icon next to Source and use the advanced options to add the select and finally delete the navigation step.
If you have 30million rows you may want to aggregate the data and limit the columns in the select and fix the report if this changes things. It's not going to be the best performing powerbi doc if you have to load that many rows on every refresh.
Hi @magic-powerbi,
I'd like to suggest you add the filter query to advanced options:
Reference links:
Differences between DB2 ODBC and embedded SQL
DB2 ODBC initialization keywords
Regards,
Xiaoxin Sheng
Hi,
I appreciate the response. I tried doing that and it works if I am doing for a new report.
But unfortunately, I already have an existing report in Power BI and it does not use the SQL to get the rows rather it uses the table directly. And I am not sure how can I change this existing connection to use the SQL so that I can get the current data and still have my reports working.
Thank you!
You can change the source from import without breaking you reports as long as the name and column list stay the same.
This is an example odbc source from the advanced editor
let Source = Odbc.DataSource("dsn=WideWorldImporters", [HierarchicalNavigation=true]), WideWorldImporters_Database = Source{[Name="WideWorldImporters",Kind="Database"]}[Data], Website_Schema = WideWorldImporters_Database{[Name="Website",Kind="Schema"]}[Data], Customers_View = Website_Schema{[Name="Customers",Kind="View"]}[Data] in Customers_View
You can change it manually in the advanced editor to something like this. Changing the query to limit to relevant rows.
let
Source = Odbc.Query("dsn=WideWorldImporters", "SELECT TOP (1000) [CustomerID]#(lf) ,[CustomerName]#(lf) ,[CustomerCategoryName]#(lf) ,[PrimaryContact]#(lf) ,[AlternateContact]#(lf) ,[PhoneNumber]#(lf) ,[FaxNumber]#(lf) ,[BuyingGroupName]#(lf) ,[WebsiteURL]#(lf) ,[DeliveryMethod]#(lf) ,[CityName]#(lf) ,[DeliveryLocation]#(lf) ,[DeliveryRun]#(lf) ,[RunPosition]#(lf) FROM [WideWorldImporters].[Website].[Customers]#(lf) order by [CustomerID] desc")
in
Source
You can click the settings cog icon next to Source and use the advanced options to add the select and finally delete the navigation step.
If you have 30million rows you may want to aggregate the data and limit the columns in the select and fix the report if this changes things. It's not going to be the best performing powerbi doc if you have to load that many rows on every refresh.
Hi, Looking over this thread I was wondering if you can help ....
I want to limit the data how I can reference a .csv table I have loaded into Power BI
-- The csv file I have uploaded has a field wr_rfa_ID and the table is called NTS_Report
--The table I would like to load is the wmis_activity_component_visit
I only need the wr_rfa_ID`s listed in the csv file not the whole table (I have tried a couple of ways - 1 example below)
SELECT
a.wr_no + a.wr_rfa_no AS wr_rfa_ID,
a.vst_rpt AS NTS_vst_rpt,
FROM prod_history_capture_open.wmis_activity_component_visit a
WHERE a.wr_rfa_ID IN (SELECT wr_rfa_ID FROM NTS_Report)
AND a.acty_cmpnent_vst_date >='2017-01-01 00:00:00.0';
Thank you in advance
This could be tricky if you have lots of IDs and data. The ideal would to have the list in the source server so you can use the database engine to filter before sending the data.
3 options I can think of are.
1) if the ID list is short create a string and build a sql command based on this in M,
e.g. IDS IN ( 3,23,56,73,86 )
wr_rfa_IDS is your csv data
let Transposed = Table.Transpose(wr_rfa_IDS), MergedIDS = Table.CombineColumns(Table.TransformColumnTypes(Transposed , {{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}}, "en-GB"),{"Column1", "Column2", "Column3", "Column4", "Column5"},Combiner.CombineTextByDelimiter(",", QuoteStyle.None),"Merged"), SQLText = "SELECT TOP (1000) start_step_id, job_id#(lf) FROM [msdb].[dbo].[sysjobs]#(lf) where start_step_id in (" & MergedIDS[Merged]{0} & ")", Source = Sql.Database(Server, Database, [Query=SQLText]) in Source
2) create a function for the retreival of each id and merge the results
fnGetIDData
let Source = (TextIDValue as text) => let SQLText = "SELECT TOP (1000) start_step_id, job_id#(lf) FROM [msdb].[dbo].[sysjobs]#(lf) where start_step_id = " & TextIDValue & " ", Source = Sql.Database(Server, Database, [Query=SQLText]) in Source in Source
let Source = wr_rfa_IDS, #"Changed Type" = Table.TransformColumnTypes(Source,{{"wr_rfa_IDS", type text}}), #"Invoked Custom Function" = Table.AddColumn(#"Changed Type", "Data", each fnGetIDData([wr_rfa_IDS])), #"Expanded Data" = Table.ExpandTableColumn(#"Invoked Custom Function", "Data", {"start_step_id", "job_id"}, {"Data.start_step_id", "Data.job_id"}) in #"Expanded Data"
So this loops through the ids bring an ID at a time.
3) Try and get the query folding to limit the rows.
Not sure if this will work but it you pull a full table
let
Source = Sql.Database(Server, Database),
dbo_MSdbms_datatype_mapping = Source{[Schema="dbo",Item="MSdbms_datatype_mapping"]}[Data],
#"Filtered Rows" = Table.SelectRows(dbo_MSdbms_datatype_mapping, each ([map_id] = 6 or [map_id] = 10 or [map_id] = 13 or [map_id] = 24 or [map_id] = 26 or [map_id] = 44 or [map_id] = 46 or [map_id] = 59 or [map_id] = 60))
in
#"Filtered Rows"
and filter it
The query folding then applies a filter to the sql run on the server.
select [_].[datatype_mapping_id],
[_].[map_id],
[_].[dest_datatype_id],
[_].[dest_precision],
[_].[dest_scale],
[_].[dest_length],
[_].[dest_nullable],
[_].[dest_createparams],
[_].[dataloss]
from [dbo].[MSdbms_datatype_mapping] as [_]
where ((([_].[map_id] = 6 or [_].[map_id] = 10) or ([_].[map_id] = 13 or [_].[map_id] = 24)) or (([_].[map_id] = 26 or [_].[map_id] = 44) or ([_].[map_id] = 46 or [_].[map_id] = 59))) or [_].[map_id] = 60
So it may be possible to apply your list of ids in a way that allows query folder to work for you. Note it's easy to do something to stop query folding working.
These posts may help
https://www.mssqltips.com/sqlservertip/3635/query-folding-in-power-query-to-improve-performance/
Thank you so much. That works!
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 |
---|---|
97 | |
94 | |
74 | |
71 | |
64 |
User | Count |
---|---|
143 | |
109 | |
103 | |
82 | |
74 |