Get data from Power Platform - Transform not available?

[rachel06]

Hi!

I'm trying to load data into Excel right from PowerBI using "Get Data > From Power Platform > From Power BI.

I can find my dataset no issue, but when I insert the data, I don't have the option to transform. When I launch PowerQuery Editor, I don't see my data, and when I go to new source > power platform, PowerBI isn't even there.

Question is - is it even possible to, from Excel, get data from PowerBI and run it through PowerQuery? There's just a few things I'd like to clean up!

Thanks!

 

[ExcelToDAX]

So you can't technically get that data directly into Power Query. But you can create a a power query to reference that internal table ( i.e. =Excel.CurrentWorkbook() ). You would have to make sure you refresh your power bi data before you refresh your Power Query table though.

 

[rachel06]

So you can't technically get that data directly into Power Query. But you can create a a power query to reference that internal table ( i.e. =Excel.CurrentWorkbook() ). You would have to make sure you refresh your power bi data before you refresh your Power Query table though.

Boo. Okay. I appreciate the response!

Another question - asked this before but never actually got to the solution (I was provided a solution but couldn't replicate). When I load the data from PBI into Excel using get data, the headers come in with both the query name and the header. So I'll have like "Query1[PAYEE NAME]" instead of just [PAYEE NAME]. Is there a way to just load it without "Query1" in the column header? That's one thing that'd clean it up a bit.

 

[RoryA]

I was provided a solution but couldn't replicate

Any reason you didn't follow that up in the original thread?

 

[rachel06]

Any reason you didn't follow that up in the original thread?

The solution seemed simple enough and I thought I could get it. Not so much! I got greedy lol

 

[ExcelToDAX]

Boo. Okay. I appreciate the response!

Another question - asked this before but never actually got to the solution (I was provided a solution but couldn't replicate). When I load the data from PBI into Excel using get data, the headers come in with both the query name and the header. So I'll have like "Query1[PAYEE NAME]" instead of just [PAYEE NAME]. Is there a way to just load it without "Query1" in the column header? That's one thing that'd clean it up a bit.

Same thing you have to do is pull it into Power Query. I tried creating complex DAX query to create a table and remove the Query1 in the column headers but I didn't have any success. When you pull it into power query, just remove them from the headers.

 

[RoryA]

If you look at the DAX for the query, near the end you should see an EVALUATE statement followed by the name of the last VAR above it (there may also be an ORDER BY after that). You basically need to amend that from

EVALUATE some_VAR_name

to

EVALUATE SELECTCOLUMNS(some_VAR_name, "new field name1", tablename[fieldname1], "new Field Name2", tablename[Field name 2])

as needed.