Get Limited data in Power BI

[magic-powerbi]

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

[v-shex-msft]

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

[magic-powerbi]

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!

[stretcharm · Accepted 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.

[mjohnsonuk]

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

[stretcharm]

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/

https://blog.crossjoin.co.uk/2017/06/11/query-folding-and-writing-your-own-sql-queries-in-power-quer...

[magic-powerbi]

Thank you so much. That works!