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
chayanupadhyay
Helper III
Helper III

Multiple sheet load in Power BI

Hi All,

 

I have one workbook with multiple sheet, PFB the format of each sheet:Capture.JPG

 

I need to load all the sheet and convert in below format:

 

Company NameCountry NamePeriodKPI

xyzCountry1Q1 20189
xyzCountry1Q2/201810
xyzCountry1Q3 20186
xyzCountry1Q4/20188
xyzCountry1Q1 20198
xyzCountry1Q2/201911
xyzCountry2Q1 20185
xyzCountry2Q2/20185
xyzCountry2Q3 20186
xyzCountry2Q4/ 201810
xyzCountry2Q1 20197
xyzCountry2Q2/ 20198

 

I am able to convert into above format (after applying all the transformation like remove column, transpose etc) but this process has to be automated, the workbook will be present on Sharepoint folder and as soon as new sheets are added on same workbook, each sheet should be converted into above format and each sheet data should append.

As per me, this looks very complex or even not feasible as part of automation but it would be great if you could share your valuable input on this and if anyone has any solution for this then please let me know.

i am hoping if i can make the steps to convert the data into above format as function and run the function through M query for each sheet but not an expert on M query so looking for your inputs.

I highly appreciate your help and time on this.

Please let me know if you need more information on above use case.

 

Thank you 🙂

3 REPLIES 3
chayanupadhyay
Helper III
Helper III

Workaround for the solution (which i should i have thought earlier but wasn't clear that i will always get 3 sheets in workbook)

Since i only had 3 sheets in workbook, i loaded the indvidual sheet in seperate table and formatted in tabular model and then combined the three tables and created as new table which will have data from all the three sheets in expected tabular format.

Not sure how to format in tablular format if if we get multiple sheets (dynamically) in same format in workbook.

But able to solve the issue in hand 🙂

 

Anonymous
Not applicable

Hi

Have you done all of the transformation inside the Powerquery editor? If so, then the process is already 'automated'.

Once you refresh the data source of your PowerBI report, it will automatically repeat all of the steps you've done in the Powerquery editor. So you simply need to schedule the refresh of your report on the PowerBI Service. 

Let me know if this helps.

@Anonymous Thank you for your reply, yes i have done all the transformation within Edit query window (Power Query) but i have done it for one sheet, i can't combine all the sheet because once i combine i am unable to convert the data in tabular format as shown earlier:

after combining the sheet1 and sheet2 the data looks in below format:Capture2.JPG

so if i delete top 7 rows and transpose the data then i will have multiple columns with Country Name and Period , PFB the snapshot for your reference: (Column2  & column13 for country names and column3 and column14 for period) and also there is lot of extra columns which i removed since i can see the data at this moment but in case of automation even if there is one extra row with some text it might impact the transformation so i will make sure the source file format remains same.Capture3.JPG

 

Apologies, the description became too big, i hope the problem statement is making sense. Please let me know if you need more information.

Thank you for your help and time.

 

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.