Multiple sheet load in Power BI

[chayanupadhyay]

Hi All,

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

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

Company NameCountry NamePeriodKPI

xyz	Country1	Q1 2018	9
xyz	Country1	Q2/2018	10
xyz	Country1	Q3 2018	6
xyz	Country1	Q4/2018	8
xyz	Country1	Q1 2019	8
xyz	Country1	Q2/2019	11
xyz	Country2	Q1 2018	5
xyz	Country2	Q2/2018	5
xyz	Country2	Q3 2018	6
xyz	Country2	Q4/ 2018	10
xyz	Country2	Q1 2019	7
xyz	Country2	Q2/ 2019	8

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 🙂

[chayanupadhyay]

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]

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.

[chayanupadhyay]

@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:

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.

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.