Transpose or Pivot Columns / Rows

[venstart]

Hi There

I have a Power BI table which is called " Log Class "

There are columns here with the main unique identifier is the column called " LoadSheetNo. " 

Currently my values are is a row, like below screenshot:

Which is a problem, I want the "Log Class" which is the "A" & "B1" to be as a column and not in a row. Currently there are 2 row since there is only 2 unique "LoadSheetNo." which are : 1517 & 1516

So the same example above data, see below how I want the data to be in this table either using Transpose or Pivot whereby now is will be : 

Columns : 4 

Rows : 3 (excl the header)

Below how it show be:

How is it now 3 rows ? -- - Because LoadSheetNo : 1517 have both Log Class "A" & "B1" and whereas Loadsheetno : 1516 only have "A" and not "B1"

How do I resolve this, please guide me as I am not an expert in Power BI

[PhilipTreacy]

Hi @venstart 

Here's the PBIX file using your text file but the transformation steps are the same as the first file I linked to.

The final result is in the Append1 query.  You'll need to modify the Source step in the other 2 queries to point to the location of your own text file.

Regards

Phil

[PhilipTreacy · Accepted solution]

Hi @venstart 

Download sample PBIX with data and queries shown here

If that sample shows all the columns in your data then you can duplicate the table, remove columns from each table that are not needed (remove A cols from B table and B cols from A table) then Append the 2 tables to give the desired result.

Regards

Phil

[vanessafvg]

can you provide sample data in text format rather than a picture?

[venstart]

Hi there

Kindly find the data in raw text file ( this is the current table)

Link to the txt file below:

https://www.file.io/download/AZ5DZAjoHtOu 

http://m.uploadedit.com/busd/1611303491147.txt