Matt Allington shows how you can pivot text data from an Excel spreadsheet using Power Query:
It is very common to need to transform data from one “shape” to another “shape” before it can be used inside Power BI for analysis (although many beginners don’t realise this). One such example is shown below, where the data in the table on the left hand side needs to be transformed into the table on the right hand side. As you can see on the left, column A contains the attribute and column B contains the value of the attribute. Every 4 lines of data is 1 record. This specific problem is very common problem when your only source of data is from an extract (eg csv) from some other system, particularly older systems where you can’t change the format of the data extract.
This is a clever solution.