Power Query- Data Cleaning (Unpivot, Transpose,etc)

Occasional Contributor

Hello Team,


I was hoping you can help with my training in power query. Im practising how to clean and transform data in power query so that I can easily manipulate and analyze the data in excel.


Can someone suggests steps on how I can achieve the following data form? I have been using index, transpose and pivot column in all sides but somehow I cannot achieve this type tabular structure. 


Thank you so much for your suggestions


convert data .jpg

4 Replies


Perhaps 3 queries

- one only with row where is Date

- in another one find on first column Accounting and Total Accounting, remove all records before and after, includes them

- append second to first, use first row as headers and return the table back to Excel

Hey thanks. Im following your suggestion. I already have the first query. Im now on the second, how do I remove the records in between Accounting and Total Accounting? This are actually dates. Do you mean replace the dates with null values?
best response confirmed by allyreckerman (Microsoft)


In Advance Editor add two lines to find positions of these texts and apply Table.Range to them like

    Source = Excel.CurrentWorkbook(){[Name="Range"]}[Content],
    Start = List.PositionOf(Source[Column1], "Accounting"),
    End =  List.PositionOf(Source[Column1], "Total Accounting"),
    #"Kept Range of Rows" = Table.Range(Source,Start+1,End-Start-1)

    #"Kept Range of Rows"


Great. Thank you so much