cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

Re: Change pbix data source programmatically

Did this work ? I get an error message saying corrupted file or file created from unsupported version of Poewr BI.

Highlighted
Frequent Visitor

Re: Change pbix data source programmatically

@v-shex-msft,

 

Can this method be used as a way to share templates / content packs to connect to different sources ?

The history behind this question is here.

Highlighted
Frequent Visitor

Re: Change pbix data source programmatically

I was able to solve this problem using a paremeter in Power Query. The parameter is set to the users dropbox "home" local folder. Then, when I share the PBT file it prompts the user to enter their specific filepath before creating their user-specific pbix. This isn't quite automagical but it does the job. 

Highlighted
New Member

Re: Change pbix data source programmatically

can we rename power bi embedded report using asp.net mvc

Highlighted
New Member

Re: Change pbix data source programmatically

Hi. I copy File pbix with the xlsx file to another computer and I want to refresh BI file using .net. How can I modify DataMashup file or another way to refresh connections? I do not want to use interface, to swap data source file. Some one of you try this before?

Highlighted
Frequent Visitor

Re: Change pbix data source programmatically

Any luck with modifying the data mashup file ?

Highlighted
Frequent Visitor

Re: Change pbix data source programmatically

Also interested in this option; here's what I've found so far:

 

1. The pbit/pbix files are basic zip files, with the extension changed; you can unzip and rezip and PowerBI will recognize them

2. The DataMashup file within the archive is also an archive, but it is NOT a basic zip file; unzipping and rezipping will not be recognized by PowerBI... but this archive contains Section1.m which has the query source definitions (likely what we need to change programmatically to achieve Jorge's desired solution)

3. DataMashup seems to be a MS-QDEFF file: https://docs.microsoft.com/en-us/openspecs/office_file_formats/ms-qdeff/e6377721-700b-411c-9d4f-3fc0...

 

So at least the potential workflow for programmatically changing the source would be:

a. upzip the pbit/pbix archive

b. parse/unpack the DataMashup file, following the MS-QDEFF structure rules

c. change Section1.m

d. repackage DataMashup, following the MS-QDEFF structure rules

e. rezip the entire archive, including the new DataMashup and change the extension back to pbit or pbix

 

Python's zipfile library can do steps (a) & (e) easily, and basic python string manipulation can handle (c). Challenge is writing some functions to handle the MS-QDEFF parsing/writing... 

 

 

Highlighted
Frequent Visitor

Re: Change pbix data source programmatically

Got it I think.

 

Here's Python code to unpack, change and repack the *.pbit programmatically (haven't tested on *.pbix):

----

import zipfile
import io

main_pbit = r"target_full_path_to_pbit/file.pbit"

revised_pbit =   r"target_full_path_to_save_pbit/file_new.pbit"


'''
Key Functions
'''    
def edit_Section1(old_text,new_text,datamashup_byte_stream):
    byte_dict = {}
    
    with datamashup_byte_stream as f:
        f.seek(0)
        #parsed bytes based on the microsoft MS-QDEFF documentation (thank you MS!)
        byte_dict['version'] = f.read(4)
        
        byte_dict['pkg_parts_len'] = f.read(4)
        byte_dict['pkg_parts'] = f.read(int.from_bytes(byte_dict['pkg_parts_len'],byteorder='little'))
        
        byte_dict['perm_len'] = f.read(4)
        byte_dict['perm_var'] = f.read(int.from_bytes(byte_dict['perm_len'],byteorder='little'))
        
        byte_dict['meta_len'] = f.read(4)
        byte_dict['meta_var'] = f.read(int.from_bytes(byte_dict['meta_len'],byteorder='little'))
        
        byte_dict['perm_bind_len'] = f.read(4)
        byte_dict['perm_bind_var'] = f.read(int.from_bytes(byte_dict['perm_bind_len'],byteorder='little'))
        
        mashup_f = io.BytesIO(byte_dict['pkg_parts'] )
        test_zip = zipfile.ZipFile(mashup_f,mode='r')
        archive_contents = test_zip.filelist
        #step into the package parts of the archive, which contains the query definitions
        with test_zip.open('Formulas/Section1.m','r') as section1_f:
            section1_text = section1_f.read()
            '''
            Do all your text swapping/manipulation here
            '''
            section1_revised = section1_text.replace(old_text,new_text)
        
        
        #create a new zip file in memory
        zip_buffer = io.BytesIO()
        zip_archive = zipfile.ZipFile(zip_buffer,mode='w',compression=zipfile.ZIP_DEFLATED)
        
        for ac in archive_contents:
            if ac.filename == 'Formulas/Section1.m':
                zip_archive.writestr('Formulas/Section1.m',data=section1_revised)
            else:
                with test_zip.open(ac,'r') as temp_f:
                    temp_bytes = temp_f.read()
                zip_archive.writestr(ac,data=temp_bytes)
        
        #close out streams   
        zip_archive.close()
        test_zip.close()
        
        #go back to start of bytestream and re-read it, finding new package parts and package parts length
        zip_buffer.seek(0)
        byte_dict['pkg_parts'] = zip_buffer.read()       
        byte_dict['pkg_parts_len'] = len(byte_dict['pkg_parts']).to_bytes(4,byteorder='little')
        
        f.close()
    
    new_mashup = io.BytesIO()
    
    #write out the revised DataMashup file (in correct order, from ordered list)
    for b in ['version','pkg_parts_len','pkg_parts','perm_len','perm_var','meta_len','meta_var','perm_bind_len','perm_bind_var']:
        new_mashup.write(byte_dict[b])
        
    return new_mashup
    

'''
Main Code
'''

main_zip = zipfile.ZipFile(main_pbit,mode='r')
main_archive_list = main_zip.filelist

new_zipfile = zipfile.ZipFile(revised_pbit,mode='w',compression=zipfile.ZIP_DEFLATED)

for al in main_archive_list:
    if al.filename == 'DataMashup':
        with main_zip.open(al.filename,'r') as datamashup_byte_stream:
            #Do any text parsing on the source definitions here
            revised_DataMashup = edit_Section1(b'CHANGETHISTOSOMETHINGELSE',b'Hello_world',datamashup_byte_stream)
            revised_DataMashup.seek(0)
            final_DataMashup = revised_DataMashup.read()
            new_zipfile.writestr('DataMashup',data=final_DataMashup)
    else:
        with main_zip.open(al,'r') as temp_f:
            temp_bytes = temp_f.read()
        new_zipfile.writestr(al,data=temp_bytes)
        
#close out streams   
main_zip.close()
new_zipfile.close()

 

----

For this demo I just made a very basic pbit file with a data source and a custom column:

 

let
     Source = ...

     ...
     #"Added Custom" = Table.AddColumn(#"Removed Other Columns", "Custom_Col", each "CHANGETHISTOSOMETHINGELSE")
in
     #"Added Custom"

 

... and it looks like we can swap out the column text "CHANGETHISTOSOMETHINGELSE"-->"Hello_World" fine.  To swap out other parts of the M query source, I think it's just a matter of the specific text parsing for section1.m 

 

Next step will be to figure out how to pull/push these files to/from webservice, but hopefully this is a useful piece for others.

Helpful resources

Announcements
Super Users of the Quarter - Q2 2020

Super Users of the Quarter - Q2 2020

Who are our Super User Superstars? Who made it to the top of the leaderboards? Get the answers!

June 2020 Community Highlights

June 2020 Community Highlights

Featured community members, changes to the Community, and more! Read up on recent Power BI community news.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Power Platform 2020 release wave 2 plan

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors