Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Create table based on certain columns from different Excel-sheets

Hello everyone,

 

I have alot of different Excel-sheets that contains in many ways the same data but the columns might differ a little.

Is it possible to create a PowerBI report which will include certain columns based on what data the Excel-sheet provides?

 

I've tried to visualise my problem below.

I want to make a PowerBI report that shows Column 1-10 based on what columns are in the company data so when I open the same report with a new company it will automatically detect what columns to visualise.

 

I hope my question makes sense - please let me know if you need further information 🙂

 

Thank you!

 

Need Help

5 Replies

  • timg's avatar
    timg
    Solution Sage
    4 years ago

    Hi Xilitor,

    I'm not a 100% sure if this is what you are looking for but it is possible to append the different tables into one in power query, so that you can visualize all columns in the same visual. Depending on which columns are filled for each company, it will return either a filled or empty value.

    Best regards,

     

    Tim

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Thank you for your response, Tim!

       

      I might've been unclear - I get your suggestion though.

      The problem is that only one Excel-sheet should be used at a time as each Excel-sheet present a company and they should not get information about other companies.

      I hope that makes sense.

       

      Thank you anyway 🙂

      • timg's avatar
        timg
        Solution Sage
        4 years ago

        Ahh oke, 

        So do I understand correctly that you only want to connect to one sheet/company. And then be able to change the connection to a different sheet/company while not having to change the queries or visuals?

         

        In that case, would it be possible to standardize the layout of the excel files so that they all have the same columns e.g. like your example table above, that all sheets contain column 1 to 10. They do not all need to be filled, but as long as the column definition is the same you could then switch the excel sheet (manually in the data source or via a parameter)  without having to make any changes in the queries. This way, you could make one visual with all columns, and depending on which sheet you connect to it will display the relevant columns filled.

        Regards,

         

        Tim