Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
cs97009
Advocate I
Advocate I

Can I edit query of a dataset from workspace while published without using PBI Desktop?

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

1 ACCEPTED SOLUTION
ImkeF
Super User
Super User

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

View solution in original post

8 REPLIES 8
ImkeF
Super User
Super User

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 !!!

ImkeF
Super User
Super User

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:

cs97009_1-1666671041092.png

 

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

mariussve1
Impactful Individual
Impactful Individual

You should look into XMLA Read/Write and Tabular Editor.

 

I think that could resolve your requierments 🙂

 

br

Marius


Br
Marius
BI Fabrikken
www.bifabrikken.no
collinq
Super User
Super User

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.




Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!
Private message me for consulting or training needs.




ImkeF
Super User
Super User

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

cs97009_0-1666594299161.png

 

Step 2 - I edited my query like below. I presume :startDate or @startDate works similarly. 

cs97009_1-1666594359645.png

Do I have to do anything else? because when I run the query, I get below error. 

cs97009_2-1666594444554.png

 

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

 

 

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors