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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
ThomasDay
Impactful Individual
Impactful Individual

Query and import from (staging .pbix) to (modeling .pbix)

Hello,

I'm wanting to query and import from a .pbix file.  Here's context:

 

For stage 1, I'm looking to build a ETLStaging.pbix file where I do transformations, cleanup, column builds if I need them, data validation, and stuff like that.  The data set or sets will be very large.

 

I then want to use that as the import sourse for queries into a modeling.pbix file where I am building hundreds of measures and so on.  Here I'll reduce the rows from the raw data set to known "schedules" of rows that I need.  The modeling.pbix will have the measures and focused slices of the data from the staging.pbix.

 

Is this possible?  I don't see .pbix as a file source type so can't find it to query it.

 

Thanks in advance for any help here,

Tom

1 ACCEPTED SOLUTION

Yes, of course can you filter out data if you are able to specify what you don't need beforehand.

 

Easiest is to merge your source data with a filter-table that contains the keys of the data you want to keep on JoinKind.Inner. That way you don't have to write a function or type in your WkshtCd(7)s by hand.

 

Actually, I would use Power Query in Excel for the staging (has more or less the same functionality - except from some data-sources I believe). It's still easier there to keep parameters for your queries. Actually, if you're using Excel 2016 you should be able to export your data via VBA from there.

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

12 REPLIES 12
ImkeF
Super User
Super User

Correct, you cannot query a pbix-file.

 

But you could export your staging tables and then query those tables. Provided that the regular refresh is manageable.

 

If we're talking about >150 k rows, then you can export your staging tables to txt via DAX-Studio: http://exceleratorbi.com.au/getting-started-dax-studio/ (manual refresh)

 

Don’t know if the ExportToSQL-feature from Power Update is or will be available for PBI, but this would be cool, as you could create schedules for automatic refreshes.

 

Scheduled refreshes from PBI services seem to be limited to 10k rows.

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

ThomasDay
Impactful Individual
Impactful Individual

Thank you @ImkeF--that's unfortunate.  The update cycle is modest but there are millions of records--quarterly and 4-10 files. I'll probably try out the DAX studio export.

 

Interestingly, I don't need most of the records--about 25%.  I probably should start a new thread, but on the off chance someone reads this...does the following query code reduce the records for one of those files to those 11 schedules I need and put them back into that same file? (i.e. remove other records)

 

---Table.SelectRows(table as table, condition as function) as table

 

Table.SelectRows( HOSP10_2014_NMRC, {
{WkshtCd(7)="A000000"},
{WkshtCd(7)="A700001"},
...repeat for other schedules...
{WkshtCd(7)="S300004"},
{WkshtCd(7)="S300005"}
}
, HOSP10_2014_NMRC)

 

And I do appreciate your and other's patience...Tom

Yes, of course can you filter out data if you are able to specify what you don't need beforehand.

 

Easiest is to merge your source data with a filter-table that contains the keys of the data you want to keep on JoinKind.Inner. That way you don't have to write a function or type in your WkshtCd(7)s by hand.

 

Actually, I would use Power Query in Excel for the staging (has more or less the same functionality - except from some data-sources I believe). It's still easier there to keep parameters for your queries. Actually, if you're using Excel 2016 you should be able to export your data via VBA from there.

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

ThomasDay
Impactful Individual
Impactful Individual

Good practical advice @ImkeF.  I've reworked stuff today to do the staging in Excel.  Not only did the reduction work great, it has given me the correct "M" syntax as well so I can try to get that under my belt.  (That's all pretty mysterious and I've only found "documentation" resources on it as opposed to coding learning books.  I guess that's part of the fun of being on the edge of tool development with the new power.bi things.)

 

I'll export into the calculation model shortly--but so far so good.

 

So far so good!  Thanks again,

Tom

 

You should check out this article: http://excel-inside.pro/blog/2015/11/05/absolute-and-relative-references-in-power-query/

and if you like it the whole blog. There you'll find some very nice explanations of basic (and advanced) M-code-concepts.

 

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

ThomasDay
Impactful Individual
Impactful Individual

@ImkeF  do you know the VBA structure off hand.  I've not written vba and am getting a colleague to help out.  Is there some special command or "table name" for the power query data tables I'm looking to export?  Thanks, Tom

No sorry, haven't tried that out yet.

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

ThomasDay
Impactful Individual
Impactful Individual

Hello All,

We've borrowed a VBA script from Kasper de Jonge--referenced in earlier posts--and modified.  The long and short is it doesn't work on a 3.3mm record file.  Excel stops responding and spins.  It works perfectly on smaller tables.  

 

Any other ideas on how to get data FROM .pbix files to another .pbix model?  Anyone exported records via "R"?  

Tom

 

The VBA script to export to might be useful to some of you...so here it is:

Option Explicit
Public Sub ExportToCsv()

Dim wbTarget As Workbook
Dim ws As Worksheet
Dim rs As Object
Dim sQuery As String

'Suppress alerts and screen updates
With Application
.ScreenUpdating = False
.DisplayAlerts = False
End With

'Bind to active workbook
Set wbTarget = ActiveWorkbook

Err.Clear

On Error GoTo ErrHandler

'Make sure the model is loaded
wbTarget.Model.Initialize

'Send query to the model
sQuery = "EVALUATE HOSP10_2014_NMRC"
Set rs = CreateObject("ADODB.Recordset")
rs.Open sQuery, wbTarget.Model.DataModelConnection.ModelConnection.ADOConnection
Dim CSVData As String
CSVData = RecordsetToCSV(rs, True)

'Write to file
Open "C:\Users\thomas\Dropbox\FTRatings\FTRatingsData-Models\ExportCSV\MYCSV.csv" For Binary Access Write As #1
Put #1, , CSVData
Close #1

rs.Close
Set rs = Nothing

ExitPoint:
With Application
.ScreenUpdating = True
.DisplayAlerts = True
End With
Set rs = Nothing
Exit Sub

ErrHandler:
MsgBox "An error occured - " & Err.Description, vbOKOnly
Resume ExitPoint
End Sub



Public Function RecordsetToCSV(rsData As ADODB.Recordset, _
Optional ShowColumnNames As Boolean = True, _
Optional NULLStr As String = "") As String
'Function returns a string to be saved as .CSV file
'Option: save column titles

Dim K As Long, RetStr As String

If ShowColumnNames Then
For K = 0 To rsData.Fields.Count - 1
RetStr = RetStr & ",""" & rsData.Fields(K).Name & """"
Next K

RetStr = Mid(RetStr, 2) & vbNewLine
End If

RetStr = RetStr & """" & rsData.GetString(adClipString, -1, """,""", """" & vbNewLine & """", NULLStr)
RetStr = Left(RetStr, Len(RetStr) - 3)

RecordsetToCSV = RetStr
End Function

 

 

ThomasDay
Impactful Individual
Impactful Individual

I tried to export via "R."  I dragged fields into the visualization area, and added the items from a "Use R to export data" page.  Here's the script. I tried the slash in both directions.  Also tried to use the require(gdata) command cited, but it wasn't recognized.

R-Script.PNG

The error I get is: Unexpected input in "in write.table(trim(dataset), file="

 

Now mind you, I'm just trying to use a power query to make a data connection to a .pbix model.  Incredible that this isn't the FIRST connection developed.  I can import ORACLE, and every other data base EXCEPT the ones I build in Power Bi.  Go figure.

 

So--any help would be appreciated...this is a hole in the product right now so I'm not quite sure what I'll do next.

Tom

Very sorry, should have tried the VBA out before leading you into the wrong direction. Looks like the VBA in 2016 can only access the M-code and automize query creation/adjustion.. but not access the data model.

 

Yesterday at the summit it has been announced that you soon will be able to access the PBI datamodel from Excel (at least if I understood this correct)

 

Until then you might try Unlimited row-export to txt via DAX Studio: http://exceleratorbi.com.au/getting-started-dax-studio/

 

(Re the R-version: Irritated by the error-message, did you install the gdata-package before?)

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

ThomasDay
Impactful Individual
Impactful Individual

Hi @ImkeF--you've been such a big help, no apologies needed.  AND I now at least know where to put VBA scripts in an Excel model...:-)  I was frustrated tho.  On the "R" question, I'm not sure where the library would be for things beyond the math library.  I have a lot of catchup learning on so many fronts, but these tools make the discovery process much more leveraged and valuable.

 

We have a statistician who is a SAS coder who we want to migrate to "R".  I was pleased to see the visualization connection even if I couldn't export data right now.  So, again, it was useful...just didn't get me to the promised land of connecting .pbix data to a next stage model.

 

So...I put the idea in the suggestion box.  Seems to me that I'll certainly be building data repositories (because I can) from so many sources of publicly available health data...and would naturally want to ETL them, work on them, and send transformed files to a consolidation model for insights generation or R visualization and so on...and then perhaps shard it up and put results behind a private login for access by others.  If I'm thinking in those terms, who isn't?  "Everyman" is now empowered to do any of this on the shoulders of the "giants" that built these tools....if only I could query my own darn models.

 

So, long diatribe to say, thanks for all your help and please vote for this idea if you find it useful too.  Tom

Thanks Tom - very interesting to see what other users do with these tools, sounds very cool!

 

Pretty sure it's "only" a matter of time before we can query desktop-models, but hey - if we can push it a bit, the better! So thanks for the initiative - got my votes already.

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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