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

[cs97009]

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

[ImkeF]

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?

[cs97009]

@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 · Accepted 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.

[cs97009]

@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

[mariussve1]

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

I think that could resolve your requierments 🙂

br

Marius

[collinq]

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.

[ImkeF]

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.

[cs97009]

@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