Convert Table variable to Text

[Anonymous]

Need: Get user name from R function:

RScript = R.Execute("output <- read.table(text=system2(""whoami"", stdout=TRUE))"),

output = RScript{[Name="output"]}[Value]

Then use that in a command:  Source = Sql.Database("MyServer", "MyDatabase", [Query="select top 1, '"& output & "' from MyTable"])

(This is merely an example and not what I will actually be doing with this variable. But the issue will be the same.)

Issue: "Output" is apparently a table variable and cannot be combined with the text, or,  I have the syntax to embed the value in the text incorrect:  Expression.Error: We cannot apply operator & to types Text and Table.

Request: How to convert "Output" into a text variable or otherwise embed the value into the  text.

Thanks

[v-yanjiang-msft]

Hi @Anonymous ,

Glad you solve the problem by yourself! Would you mind accept your reply as solution? Then we are able to close the thread. More people who have the same requirment will find the solution quickly and benefit here, thank you!

Best Regards,
Community Support Team _ kalyj

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Actually I think I jumped the gun just a bit.

Now Power Bi is telling me that, "This query contains transformations that can't be used for DirectQuery." I"ll have to puzzel that out this morning.

This is in a simple test app and there are only two queries...one returns the user name via R and the other is just getting some data and plugging the user name into the SQL query to prove it can be done. So hopefully I can figure it out today.

Stay Tuned.

[Anonymous]

So I figured it out. Hope this helps someone else.

1. Take the R script and plug it into a second query.:

let
RScript = R.Execute("output <- read.table(text=system2(""whoami"", stdout=TRUE))"),
output = RScript{[Name="output"]}[Value]
in
output

Hat tip:

https://querypower.com/2017/04/03/4-ways-to-get-username-in-power-query/

2. Change some settings:

https://sqlitybi.com/formula-firewall-error-referencing-queries-r-python-scripts/

I choose Option 2, privacy settings.

3.  IN the table you will be using the information in, use query{0}[columnname] to get value from the new query.

let
Username = Query2{0}[V1],
Source = Sql.Database("Telusdw", "Enterprise_Reporting", [Query="select top 1 *, '"& Username &"' from c.DynamicsCRMService_WO_WorkOrder"])
in
Source

[Anonymous]

I ran into an issue, "This query contains transformations that can't be used for DirectQuery."

And of course in trying to fix that I broke everything else. So I started over again. I will start over on this thread again too.

My ultimate goal is to log the user id of the person refreshing the report, the report name, date, time, etc.

Power Bi has several methods for tracking report usage, but they are either too cumberson (pick a report and get the data) or requires unobtainable administrator rights for the many services involved.

I do have the rights to create tables and stored procedures so that is how this solution will use.

Using a small R script (citation is in previous posts in this thread) I am able to get the user id in a Power M query. This is returned in a one row, single column table. Then, using the table{0}Columnname command I can get the value into a variable in Power M and include that in a second query which will invoke a stored procedure, passing the user ID as well as other information to be logged. The stored procedure will need to return some token amount of data to keep Power Bi happy.

Power Bi is pretty damned finiky and I've started over many times. So here is the process I followed that seems to be repeatable and provides, so far, a  solution for deriving the userid and including that in a SQL command (Haven't don ethe Stored procedure yet).

1. Make a new PBI document.

2. In settings, change the privacy level (see #2 in previous post)

3. Create a new dataset for SQl Server. Make it "Import" and just pick some table. We will discard the Power M Query it wil create so it doesn't mater what you pick.

4. After the datasource is created, go to Transform Data, click the datasource, and open the Advanced Editor.

5. Paste in the R script (see #1 in previous post)

6. Click Close and Apply. Be sure the changes are all applied. I threw the result into a table just to be sure I getting the right value returned).

7. Make another data source for SQL server. Also make it "Import". Pick the table you want.

8. After the datasource is created, again go to Transform Data and click the new datasource and open the Advanced Editor.

( see #3 in previous post for what I did. What you do will be different)

9. Before the "Source" statement, add in "Variablename= Datasetname{0}[V1],". This gets the value in the first row and column (Your user ID).

10. In the Source line, edit your SQL to include the variable as needed. Key syntax is the:

"sql command stuff  ' "& variablename &" ' more SQL stuff". (This is pretty much how you'd build a series of SQL commands in Excel if you were creating skads of statements based on a list of values).

11. Close and apply.

12. Test to make sure your refresh all works correctly.

Good luck.