Pivot Excel Need help

%3CLINGO-SUB%20id%3D%22lingo-sub-1600318%22%20slang%3D%22en-US%22%3EPivot%20Excel%20Need%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1600318%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20All%20can%20extract%20my%20below%20file%20using%20power%20query%20form%20this%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EInput%20Data%20Below%3C%2FP%3E%3CP%3EDate%20%26amp%3B%20TimeLocationSystemEquipmentTypeStatusStatus%20Type%3C%2FP%3E%3CTABLE%20width%3D%22605%22%3E%3CTBODY%3E%3CTR%3E%3CTD%3E31%2F03%2F2020%2023%3A42%3C%2FTD%3E%3CTD%3EBLV%3C%2FTD%3E%3CTD%3EESC%3C%2FTD%3E%3CTD%3EESC-01%3C%2FTD%3E%3CTD%3EEscalator%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3BMoving%20Status%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3BStopped%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E31%2F03%2F2020%2023%3A42%3C%2FTD%3E%3CTD%3EBLV%3C%2FTD%3E%3CTD%3EESC%3C%2FTD%3E%3CTD%3EESC-01%3C%2FTD%3E%3CTD%3EEscalator%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3BMoving%20Status%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3BMoving%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E31%2F03%2F2020%2023%3A26%3C%2FTD%3E%3CTD%3EBLV%3C%2FTD%3E%3CTD%3EESC%3C%2FTD%3E%3CTD%3EESC-01%3C%2FTD%3E%3CTD%3EEscalator%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3BMoving%20Status%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3BStopped%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E31%2F03%2F2020%2023%3A25%3C%2FTD%3E%3CTD%3EBLV%3C%2FTD%3E%3CTD%3EESC%3C%2FTD%3E%3CTD%3EESC-01%3C%2FTD%3E%3CTD%3EEscalator%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3BMoving%20Status%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3BMoving%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E31%2F03%2F2020%2023%3A09%3C%2FTD%3E%3CTD%3EBLV%3C%2FTD%3E%3CTD%3EESC%3C%2FTD%3E%3CTD%3EESC-01%3C%2FTD%3E%3CTD%3EEscalator%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3BMoving%20Status%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3BStopped%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E31%2F03%2F2020%2023%3A09%3C%2FTD%3E%3CTD%3EBLV%3C%2FTD%3E%3CTD%3EESC%3C%2FTD%3E%3CTD%3EESC-01%3C%2FTD%3E%3CTD%3EEscalator%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3BMoving%20Status%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3BMoving%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eto%20this.%3C%2FP%3E%3CP%3EOutput%20Data%20Below%3C%2FP%3E%3CTABLE%20width%3D%22319%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%22104%22%3E%26nbsp%3BStopped%3C%2FTD%3E%3CTD%20width%3D%22104%22%3E%26nbsp%3BAlarm%3C%2FTD%3E%3CTD%20width%3D%22111%22%3EMoving%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E31%2F03%2F2020%200%3A42%3C%2FTD%3E%3CTD%3E1%2F04%2F2020%2011%3A42%3C%2FTD%3E%3CTD%3E31%2F03%2F2020%2023%3A42%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20tried%20using%20Pivot%20function%20in%20power%20query%20but%20couldn't%20shape%20into%20the%20above%20output.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1600318%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1600752%22%20slang%3D%22en-US%22%3ERe%3A%20Pivot%20Excel%20Need%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1600752%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F735618%22%20target%3D%22_blank%22%3E%40Jeev125%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ELogic%20is%20not%20clear%2C%20concrete%20data%20in%20Output%20of%20your%20sample%20have%20nothing%20common%20with%20Input.%20However%2C%20you%20may%20Group%20By%20status%20without%20aggregation%2C%20keep%20only%20lists%20with%20time%20in%20output%20and%20after%20that%20create%20Table.FromColumns%20using%20above%20lists%20as%20columns.%20Please%20check%20in%20atached.%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20757px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F213416i2874789D4BC2096C%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Contributor

Hi All can extract my below file using power query form this 

 

Input Data Below

Date & TimeLocationSystemEquipmentTypeStatusStatus Type

31/03/2020 23:42BLVESCESC-01Escalator  Moving Status  Stopped
31/03/2020 23:42BLVESCESC-01Escalator  Moving Status  Moving
31/03/2020 23:26BLVESCESC-01Escalator  Moving Status  Stopped
31/03/2020 23:25BLVESCESC-01Escalator  Moving Status  Moving
31/03/2020 23:09BLVESCESC-01Escalator  Moving Status  Stopped
31/03/2020 23:09BLVESCESC-01Escalator  Moving Status  Moving

 

 

to this.

Output Data Below

 Stopped AlarmMoving
31/03/2020 0:421/04/2020 11:4231/03/2020 23:42

 

I tried using Pivot function in power query but couldn't shape into the above output.

1 Reply
Highlighted

@Jeev125 

Logic is not clear, concrete data in Output of your sample have nothing common with Input. However, you may Group By status without aggregation, keep only lists with time in output and after that create Table.FromColumns using above lists as columns. Please check in atached.

image.png