Power bi and excel

[Anonymous]

Hello I am very new to power bi I am trying to find a way to connect my excel directly to power bi which when I looked online said I can publish my excel file to power bi and use the data set to create reports.

Now when I publish the excel as data set I encounter few issues

1. I am not able to edit the data set to use first row as column name so when creating the report it shows column 1,2 etc
2. Not all of my rows are getting exported in data set 

Am i doing something wrong here, please help

[v-jingzhang · Accepted solution]

Hi @Anonymous 

It is more suggested to create a report in Power BI Desktop rather than in the service because there are many important features only existing in Power BI Desktop, including transforming and shaping data, creating calculated columns and measures, creating relationships, etc. 

If you don't want to create a report now, you can also publish it into a workspace although the report page is blank. After publishing it to a workspace, you can log in to app.powerbi.com with the same account and enter the same workspace. You will find the new dataset under Datasets + dataflows section. It has the same name as the .pbix file. You can use it to create reports later.

For the last question, in order for the report to take all the changes made in the file automatically, the dataset needs to be refreshed in the service. It depends on where the Excel file is stored. If it is on an on-premises computer, you will need to use on-premises data gateway to refresh the dataset at a scheduled time. If it is stored in the cloud (Sharepoint or Onedrive for business), it will detect the changes in Sharepoint and Onedrive, and refresh the dataset automatically. 

Refresh a dataset created from an Excel workbook - cloud - Power BI | Microsoft Docs

Refresh a dataset created from an Excel workbook - local - Power BI | Microsoft Docs

Hope this helps.

Community Support Team _ Jing
If this post helps, please Accept it as the solution to help other members find it.

[Anonymous]

@selimovd  Thank you for the reply

so as you said I uploaded my excel in power bi desktop by 'get data' then transformed it, I am here now when I close and save it takes me to create the report but all I want to do from here it to upload it as a data set in cloud. 

and use it later to create a report 

also from there we update this excel monthly will it be possible that the report takes all the changes made in the file automatically?

Hope this makes sense😅

[selimovd]

Hey @Anonymous ,

I'm not really sure what you were doing. You should go to "Get Data", choose "Excel" and import the data to Power BI. In the Power Query editor where you import the data you can do all the transformations and at the end apply them to the file.

When you then upload the file to Power BI Service you can connect to that data set with other reports or other tools.

Otherwise a few screenshots always help to see if you did a mistake somewhere 😉

If you need any help please let me know.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards

Denis

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic