May 31 2021 12:57 AM
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
May 31 2021 01:09 AM
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
May 31 2021 01:23 AM
May 31 2021 02:16 AM
SolutionIn 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"
May 31 2021 02:45 AM
May 31 2021 02:16 AM
SolutionIn 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"