Forum Discussion
Power Query- Data Cleaning (Unpivot, Transpose,etc)
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
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"
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?
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"
