Forum Discussion
Importing Microsoft Fabric Notebook Data into Power BI
I found the solution and want to share it step by step.
In Fabric - Lakehouse click on get data, upload files.
Choose your excel file.
Once you upload your excel file. You will see it on your files.
Click on top open notebook and choose new notebook.
Paste below code into your notebook.
# 1 load excel file into lake house via import data# 2 import excel file into dataframe, choose path from files ... copy ABFSimport pandas as pddf=pd.read_excel('abfss://ecf5a729-f8d9-417f-ae28-8ed1e9473ce3@onelake.dfs.fabric.microsoft.com/027295a6-aee0-4e87-a2dc-0f89fd816a27/Files/Test.xlsx')#display(df)# 3 save dataframe into parquet, choose path where want to save file using ...# 4 if parquet not installed, will get error, in this case: !pip install parquetdf.to_parquet('abfss://ecf5a729-f8d9-417f-ae28-8ed1e9473ce3@onelake.dfs.fabric.microsoft.com/027295a6-aee0-4e87-a2dc-0f89fd816a27/Files/Test.parquet')#5 read parquet filedfp=spark.read.parquet('abfss://ecf5a729-f8d9-417f-ae28-8ed1e9473ce3@onelake.dfs.fabric.microsoft.com/027295a6-aee0-4e87-a2dc-0f89fd816a27/Files/Test.parquet')# 6 write parquet into a table, go to lakehouse, from top right click on Lakehouse, change it to SQL End Poin and start using SQL on the table.dfp.write.mode("overwrite").format("delta").saveAsTable("N_G_Table")As explained and show above, you need to change path in your workspace.
once you have update tha path, run the code.
refresh your table as below.
You should see below table on your Lakehouse.
Open Power BI Desktop. You can choose lakehouses but it gave me error at the beginning, in this case click directly on OneLake data hub on Home tab.
Choose your lake house and click connect.
Created a basic chart to see that data has been loaded into power bi.
Super UserWhat are the files you want to get from PowerBi? are they a few xlsx? I thought we were talking about many files at a lake.
I have sent you an example. Of course it won't unless you change the variables.
Let's see:
spark_frame.write.format("delta").mode("overwrite").save("Tables/[TableName]")
spark_frame is a variable. It's a frame I have loaded with a file at the lake. You need to read xlsx with pyspark to get your own frame. Store it in a variable and then try the line with the name of your variable.write....
Also check that at the end it says [TableName]. You should replace that with the table name like "Sales".
If you think this spark thing is too much, we can think about other solutions. If you just want to move an excel from local to a Table at lakehouse you can use Dataflow gen2 that will be using power query only to store it at Tables so you can use it later without coding too much. I have said that because your message sounded like you manage/write notebooks.
I hope this make sense 🙂
I found the solution and want to share it step by step.
In Fabric - Lakehouse click on get data, upload files.
Choose your excel file.
Once you upload your excel file. You will see it on your files.
Click on top open notebook and choose new notebook.
Paste below code into your notebook.
As explained and show above, you need to change path in your workspace.
once you have update tha path, run the code.
refresh your table as below.
You should see below table on your Lakehouse.
Open Power BI Desktop. You can choose lakehouses but it gave me error at the beginning, in this case click directly on OneLake data hub on Home tab.
Choose your lake house and click connect.
Created a basic chart to see that data has been loaded into power bi.