SOLVED

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

Copper 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

@Poly2021 

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)
Solution

@Poly2021 

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

let
    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)

in
    #"Kept Range of Rows"

 

Great. Thank you so much
1 best response

Accepted Solutions
best response confirmed by allyreckerman (Microsoft)
Solution

@Poly2021 

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

let
    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)

in
    #"Kept Range of Rows"

 

View solution in original post