Create multiple Tables from Python

[theBIbutler]

Hi,

Using Python I'm connecting to Google Cloud Storage, I want to be able to loop through the various files in a bucket, create pandas dataframes for each one and then use the contents in Power Query.

I can get it all working in Pycharm with the below...

for file in blobs:
    file_path = "gs://{}/{}".format(file.bucket.name, file.name)
    with fs.open(file_path) as f:
        df = pd.DataFrame
        df.name = file.name
        df = pd.read_csv(f)
        print(df.name)
        print(df)

it loops through and prints the contents and name of each of the 3 files in the bucket, but in Power Query I only pull through the last iteration with the name df. 

I've tried various angles using dictionaries and lists but can't figure out how to end up with three separate tables.  I've seen examples concatenating various files but I want to keep these all separate.  I want to be able to loop through the files in the bucket because there will eventually be hundreds, so don't want to hard code each one.

Any ideas?  Is it possible?

Cheers

Mark

[Anonymous · Accepted solution]

Hi,

I'm not sure is this thread still relevant. I had the same issue and I solved this by associating each data frame to a unique global variable:

for file in blobs:
    file_path = "...."
    with fs.open(file_path) as f:
        globals()[f"{file.name}"] = pd.read_csv(f)
        print(globals()[f"{file.name}"])

[theBIbutler]

Thank you mate, I've moved company so not working on that solution anymore but this might prove handy in the future!

[v-kelly-msft]

Hi @theBIbutler ,

How about filter table name to seperate them one by one after you have loaded all to Power bi?

Best Regards,
Kelly

Did I answer your question? Mark my reply as a solution!

[theBIbutler]

Not sure what you mean...it's the loading them to Power BI part that I have a problem with...

I can only see one table in Power Query, I want to have a list of all the tables representing the files that are in the GCS Bucket...

[v-kelly-msft]

Hi @theBIbutler ,

Sorry for the misunderstanding.

Are the python statements full?Have you defined "blobs" first?

Best Regards,
Kelly

Did I answer your question? Mark my reply as a solution!

[Syndicate_Admin]

Yes 'blobs' is defined first....I didn't post all the python....

Like I said, it works fine in PyCharm in that it prints each file name and then the contents.

But when I use this script in Power Query I just get a table for the last iteration of the loop...I would like to see a table for each of the three files in GCS if that's possible?

[theBIbutler]

Yes 'blobs' is defined first....I didn't post all the python....

Like I said, it works fine in PyCharm in that it prints each file name and then the contents.

But when I use this script in Power Query I just get a table for the last iteration of the loop...I would like to see a table for each of the three files in GCS if that's possible?

[v-kelly-msft]

Hi  @theBIbutler ,

Try to use Python IDE with Power bi,check below reference:

https://docs.microsoft.com/en-us/power-bi/connect-data/desktop-python-ide

Best Regards,
Kelly

Did I answer your question? Mark my reply as a solution!

[theBIbutler]

Thanks but I don't see what dfference that would make?  I am using a Python IDE.  I'm not using the Python visual, I'm using the Python Data Connector which doesn't have any interaction with a specified external IDE:

A bit like in this doc:

https://docs.microsoft.com/en-us/power-bi/connect-data/desktop-python-scripts

[v-kelly-msft]

Hi @theBIbutler ,

I havent tried it,but based on my undertstanding,you should first set the IDE you need in Power bi desktop:

Then in the python script eidtor,click the button below:

Any script you created in the Python script editor pane of Power BI Desktop appears starting in line 4 in your Python IDE. At this point, you can create your Python script in the Python IDE. Once your Python script is complete in your Python IDE, you need to copy and paste it back into the Python script editor pane in Power BI Desktop, excluding the first three lines of the script that Power BI Desktop automatically generated. Do not copy the first three lines of script back into Power BI Desktop, those lines were only used to import your data to your Python IDE from Power BI Desktop.

Try to take the above steps to check whether it will solve the issue.

Best Regards,
Kelly

Did I answer your question? Mark my reply as a solution!

[theBIbutler]

Hi Kelly, 

What you have described applies to working with the Python Visual, in this case I'm not doing that...I'm working with the Power Query Python data connector....as described in the doc I linked above.

FWIW I have specified an IDE (and used it previously) in the Options.

[v-kelly-msft]

Hi  @theBIbutler ,

I just wanna know if it's a Python.Execute issue,since it works fine in your IDE.

I cant reproduce your senario,neither I cant find related document or issue reported,could you pls post your issue via below link and our relevant personnel will help to deal with it.

Support | Microsoft Power BI

Best Regards,
Kelly

Did I answer your question? Mark my reply as a solution!