Forum Discussion

iharriague's avatar
iharriague
Regular Visitor
3 years ago

Converting one table with mixed column and row data into 2 separate tables

I would like to please convert the orignal data table as shown below into 2 separate data tables.

I hope you can please me a hand, I am clueless on how to proceed.
I color coded the columns names for convenience.
The original talbe is built by importing excel wookbooks with 3 sheets each.

Thank you.

Need Help

3 Replies

  • edhans's avatar
    edhans
    Community Champion
    3 years ago

    This looks relatively simple.

    First just transpose the first table and promote the headers, and remove any nulls.

    Then add Table1 and Table2 as a crossjoin. This is what I got. I didn't type in all the data. Just the first stuff. The rest is just repeating and possibly appending later with Table.Combine (in the Merge menu, Append.

     

    For the first green table, New Table 1, here is what I did.

    1. New Table 1 starts as a reference of Table 1.
    2. Added a new Custom Column
    3. The formula is simply = Table 2. 

    Then just expand all columns.

     

    Here is my file. I did it all in Excel for ease of use, and didn't use multiple sheets. If you need more help, provide sample data per below.

    How to get good help fast. Help us help you.

    How To Ask A Technical Question If you Really Want An Answer

    How to Get Your Question Answered Quickly - Give us a good and concise explanation
    How to provide sample data in the Power BI Forum - Provide data in a table format per the link, or share an Excel/CSV file via OneDrive, Dropbox, etc.. Provide expected output using a screenshot of Excel or other image. Do not provide a screenshot of the source data. I cannot paste an image into Power BI tables.

     

    • iharriague's avatar
      iharriague
      Regular Visitor
      3 years ago

      Dear edhans,
      Thank you for your immediate reply.
      I believe I oversimplified my question.
      I will provide sample data to better explain the concern.

    • iharriague2's avatar
      iharriague2
      New Member
      3 years ago

      Hello edhans 
      I have saved sample data and .pbix file - see here.
      My target:
      The <Input> files will be transfromed into 2 data tables so that they replicate the <Output> excels' format.
      Get data is from a folder where the Input worksheets are stored, new ones are saved periodically.

      End goal:
      Once the data is clean I will be able to create 2 matrix (side by side) and for example understand the changes between data table 1 released x months ago, and the latest one for Model Y and Part 100.

      Thank you.
      Logic