Forum Discussion

Jeet-JK's avatar
Jeet-JK
Frequent Visitor
4 years ago

Combining Multiple excel file with Metrix data in them

Hi All

 

I am trying to figure out the solution for combining multiple excel files available on the sharepoint site, each file has data for a month and I need to import the data into Power Bi for all the files available in the sharepoint folder. however the challages here is that the data for each file available in the metric format as shown below:

File 1:  for Jan Month

File 2 : for Feb Month

when combining these files the dates will come as row in the table and now I am fining difficult to transform this data the desired format as shown below:

Desired Data Format: 

Thank you for your support and help in advance, your suggestion on this would be very helpful.

Need Help

2 Replies

  • MahyarTF's avatar
    MahyarTF
    Icon for Memorable Member rankMemorable Member
    4 years ago

    Hi,

    I created the sample for myself in Excel and did as below :

    At first I load the data to the Power Query :

    1- In Power Query, for each file (table), 'Use First Row as Headers' :

    2- Select the 'New York' Column, right-click on the title, and select 'Unpivot other columns':

     

    3- in the last level, drag the 'Attribute' column to the first column (reorder the column position):

    4- Append the tables :

     

    Thanks for your Kudos 

  • Jeet-JK's avatar
    Jeet-JK
    Frequent Visitor
    4 years ago

    MahyarTF Thank you for your reply, Appreciate it.

    The challange I am facing here is, since the files are present in the SharePoint folder and there are many file with monthly data so can not import them individually hence need to combine them before I can apply any transformation on them.

    And after combining these files the dates will come as a row in the data set which is getting difficult to split out.