SOLVED

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

%3CLINGO-SUB%20id%3D%22lingo-sub-2400300%22%20slang%3D%22en-US%22%3EPower%20Query-%20Data%20Cleaning%20(Unpivot%2C%20Transpose%2Cetc)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2400300%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20Team%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20was%20hoping%20you%20can%20help%20with%20my%20training%20in%20power%20query.%20Im%20practising%20how%20to%20clean%20and%20transform%20data%20in%20power%20query%20so%20that%20I%20can%20easily%20manipulate%20and%20analyze%20the%20data%20in%20excel.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECan%20someone%20suggests%20steps%20on%20how%20I%20can%20achieve%20the%20following%20data%20form%3F%20I%20have%20been%20using%20index%2C%20transpose%20and%20pivot%20column%20in%20all%20sides%20but%20somehow%20I%20cannot%20achieve%20this%20type%20tabular%20structure.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20so%20much%20for%20your%20suggestions%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22convert%20data%20.jpg%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F285064i41A5D0901A1160E9%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22convert%20data%20.jpg%22%20alt%3D%22convert%20data%20.jpg%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2400300%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3ECharting%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ETraining%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2400332%22%20slang%3D%22en-US%22%3ERe%3A%20Power%20Query-%20Data%20Cleaning%20(Unpivot%2C%20Transpose%2Cetc)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2400332%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1067340%22%20target%3D%22_blank%22%3E%40Poly2021%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EPerhaps%203%20queries%3C%2FP%3E%0A%3CP%3E-%20one%20only%20with%20row%20where%20is%20Date%3C%2FP%3E%0A%3CP%3E-%20in%20another%20one%20find%20on%20first%20column%20Accounting%20and%20Total%20Accounting%2C%20remove%20all%20records%20before%20and%20after%2C%20includes%20them%3C%2FP%3E%0A%3CP%3E-%20append%20second%20to%20first%2C%20use%20first%20row%20as%20headers%20and%20return%20the%20table%20back%20to%20Excel%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2400375%22%20slang%3D%22en-US%22%3ERe%3A%20Power%20Query-%20Data%20Cleaning%20(Unpivot%2C%20Transpose%2Cetc)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2400375%22%20slang%3D%22en-US%22%3EHey%20thanks.%20Im%20following%20your%20suggestion.%20I%20already%20have%20the%20first%20query.%20Im%20now%20on%20the%20second%2C%20how%20do%20I%20remove%20the%20records%20in%20between%20Accounting%20and%20Total%20Accounting%3F%20This%20are%20actually%20dates.%20Do%20you%20mean%20replace%20the%20dates%20with%20null%20values%3F%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2400455%22%20slang%3D%22en-US%22%3ERe%3A%20Power%20Query-%20Data%20Cleaning%20(Unpivot%2C%20Transpose%2Cetc)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2400455%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1067340%22%20target%3D%22_blank%22%3E%40Poly2021%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIn%20Advance%20Editor%20add%20two%20lines%20to%20find%20positions%20of%20these%20texts%20and%20apply%20Table.Range%20to%20them%20like%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-powerquery%22%3E%3CCODE%3Elet%0A%20%20%20%20Source%20%3D%20Excel.CurrentWorkbook()%7B%5BName%3D%22Range%22%5D%7D%5BContent%5D%2C%0A%20%20%20%20Start%20%3D%20List.PositionOf(Source%5BColumn1%5D%2C%20%22Accounting%22)%2C%0A%20%20%20%20End%20%3D%20%20List.PositionOf(Source%5BColumn1%5D%2C%20%22Total%20Accounting%22)%2C%0A%20%20%20%20%23%22Kept%20Range%20of%20Rows%22%20%3D%20Table.Range(Source%2CStart%2B1%2CEnd-Start-1)%0A%0Ain%0A%20%20%20%20%23%22Kept%20Range%20of%20Rows%22%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
New 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