Forum Discussion
Converting one table with mixed column and row data into 2 separate tables
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.
- New Table 1 starts as a reference of Table 1.
- Added a new Custom Column
- 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.
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