Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
Dear Experts,
Here is the usecase:
I have a large dataset with millions of rows per month that takes 30min per month to load the dataset. My goal is to build a dataset with several years of data (36 months) and published in workspace.
Here is what I thought could work, if I could edit query of a dataset from workspace while dataset is published in the workspace:
1. Build PBI dashboards using a dataset with only few months of data
2. Apply Changes to queries (Refresh with few months of data)
3. Publish the dataset and dashboards to workspace
4. Edit Query in the published dataset to include more months than the few months I have been using to build & test the PBI dashboard
5. Load several years of data using a PBI gateway over the weekend without including by local Desktop/Laptop
Is step #4 supported by PowerBI? If so, how? If not, is there another way I could achieve above? Any guidance would be much appreciated !
I can't use DirecQuery and need to use Import since some of the other functions I need seem won't work with DirectQuery.
Regards,
Jacob
Solved! Go to Solution.
Hi @cs97009 ,
in an ordinary SQL query, this would work like so:
Sql.Database(
"YourConnectinString",
"YourDBName",
[Query="select * from [dbo].[PlanningCalendar] as [_]
where [_].[Due_Date] > convert(date, ' " & Text.From(StartDate) & " ')"])
So the red part is how you integrate a parameter from Power Query into a query string.
As that is text, you need to use the double quotes as an escape sign. The & is a concatenator operator. And if your parameter is a date (instead of text) then you have to convert it to text first, before you can concatenate it with the query string.
Make sure to check your regional settings when doing the date-to-text conversion so you don't mix month and day.
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
Hi @cs97009 ,
I am having problems to understand what is going on here:
In your last picture where you have "& Text.From(StartDate) &" in multiple rows: Is that the result of your SQL-query or part of a preparation where you want to make the SQL call for each row of the table?
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
@ImkeF Looks like our replies crossed. I was using the PowerQuery GUI to input the SQL query, in which case, the PBI GUI would add an additional double quote (") escape character that was preventing the replacement with Parameter value. Once I went to advanced query editor and then removed the extra double quote ("), then I was able to replace the actual value. Thanks again for your help !!!
Hi @cs97009 ,
in an ordinary SQL query, this would work like so:
Sql.Database(
"YourConnectinString",
"YourDBName",
[Query="select * from [dbo].[PlanningCalendar] as [_]
where [_].[Due_Date] > convert(date, ' " & Text.From(StartDate) & " ')"])
So the red part is how you integrate a parameter from Power Query into a query string.
As that is text, you need to use the double quotes as an escape sign. The & is a concatenator operator. And if your parameter is a date (instead of text) then you have to convert it to text first, before you can concatenate it with the query string.
Make sure to check your regional settings when doing the date-to-text conversion so you don't mix month and day.
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
@ImkeF UPDATE. Please ignore below. I was using the PowerQuery GUI to input the SQL query, in which case, the PBI GUI would add an additional double quote (") escape character that was preventing the replacement with Parameter value. Once I went to advanced editor and then removed the extra double quote ("), then I was able to replace the actual value. Thanks again for your help
----
@ImkeF THANK YOU ! Pardon me if this is a newbie question, but I am having a hard time forcing PBI to substitute the actual parameter value into my SQL query string that is argument to TeraData.Database() function
To test, I created a Teradata SQL Query is like
SELECT
col1
,col2
,...
,'"& Text.From(StartDate) &"' as sdates
FROM SLSVIEWS.cmbnd_intl_rev intl
This teradata SQL is called from PowerQuery in PBI like below:
let
Source = Teradata.Database("ecdwp.it.att.com", [HierarchicalNavigation=true, CommandTimeout=#duration(0, 8, 0, 0), Query=".......above query....."])
in
#Source
Result is below, which implies that formula has merely been copied and formula has not been executed and replaced with value:
I am wondering how I can get the actual text '20220701' substituted by PBI into the SQL portion of PowerQuery statement.
Thanks in advance!
Regards,
Jacob
You should look into XMLA Read/Write and Tabular Editor.
I think that could resolve your requierments 🙂
br
Marius
Hi @cs97009 ,
I think the best solution for thus is to create a dataflow. 6oi van break it up into parts or use Incremental refresh. Either way, the data will be there and will be usable without having to use desktop.
Proud to be a Datanaut!
Private message me for consulting or training needs.
Hi @cs97009 ,
the only elements from a dataset that you can adjust in the service are parameters. So you could use a parameter to filter the first x months in Power Query for example. Set it to 1 and load just one month of data into the model in Desktop. Publish and change the parameter in the service to load the full amount.
Of course, using a date (until) filter would also work just fine.
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
@ImkeF Thanks for your assistance. Your proposal looks intriguing. I am, however, a bit lost on how to add the parameter to my SQL query in PBI to a Teradata server.
Step 1 - I created a startDate Parameter like below
Step 2 - I edited my query like below. I presume :startDate or @startDate works similarly.
Do I have to do anything else? because when I run the query, I get below error.
When I looked at the arguments for Teradata.Database(..) function, there are no arguments for parameters, so presume I need to let the query know that startDate has to be picked up from PBI parameter. Wonder how I can do that.. Would appreciate any guidance you can provide. Thanks !
Regards,
Jacob
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.