Forum Discussion

Ah_saaah01's avatar
Ah_saaah01
Helper I
3 years ago

data from different spreadsheets(multiple tables) in different excels, load only few columns

Hi Exports, 

 

I am try to build a past project summery from different excel files. I could format each excels with the identical strucutures. Most cases online shows how to combine all spreadsheets which are identical, however my project excels are designed to collect both inputs assumptions and results with different spreadsheets/tables, some have two row of data, some have over 8000 rows of data. 

For example:

Excel A consists spread sheet name 1, 2, 3

Excel B consists spread sheet name 1, 2, 3

....

 

In this case, I would import all A1, B1 ...(same format) into one table, A3,B3... (same format) into one table, for spreadsheet number 2, I only want to somehow using formula to exact one sum from one or two of the columns in A2, B2.....(same format) and list those as one table, to avoid bring in all the data.  

 

Could I achieve this when using PowerBI? Please help. 

 

Need Help

3 Replies

  • v-xiaotang's avatar
    v-xiaotang
    Community Support
    3 years ago

    Hi Ah_saaah01 

    Thanks for reaching out to us.

    That is possible, you can refer to these tutorials,

    (1) Power BI DAX | Beginner's Guide to DAX Functions in Power BI (wallstreetmojo.com)

    (2) Power BI DAX Tutorial for Beginners | DataCamp

    (3) Data Analysis Expressions (DAX) Reference - DAX | Microsoft Learn

     

    Best Regards,

    Community Support Team _Tang

    If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

    • Ah_saaah01's avatar
      Ah_saaah01
      Helper I
      3 years ago

      Sorry I didn't put it really clear, I think I am not asking DAX to calculate a column, but before loading the excel table A2, B2, I would want to use some function to choose maybe one or two specific column from spreadsheet no.2 of all excel files, or create new table based on a summery of only few 

      specific columns located in the same place in multiple excels. I am trying to avoid bring all the spreadsheet table into powerBI. 

       

      I guess it is M-language when importing data, (sorry still not so sure). Could I manage this with M? 

      • v-xiaotang's avatar
        v-xiaotang
        Community Support
        3 years ago

        Hi Ah_saaah01 

        Thanks for your reply.

        When you click get data - excel, you cannot choose to import only a single column, but you can remove unnecessary columns in Power Query Editor, and then Power BI Desktop will not load other unnecessary columns

         

        Best Regards,

        Community Support Team _Tang

        If this post helps, please consider Accept it as the solution to help the other members find it more quickly.