none
MS EXCEL : Help with Pivoting data RRS feed

  • Question

  • I have a data set and have to load to a database. But the data isn't in the same format as in the table in database and the table design cannot be changed. This is for historical data load.

    Here is the sample data and desired result: here

    I tried pivot table in excel but I am unable to get the desired result. If anyone can help me that'd be much appreciated. 

    I'd like to know how I can achieve this in excel..

    Thank you,

    Success



    Friday, April 19, 2019 9:12 AM

Answers

  • Hi Success t0rres,

    If shape of your data is always same (8 rows for each date), then you may use following pattern. I guess, performance of this code is not the best and for large datasets you may need another approach.

    let
        Source = Excel.Workbook(File.Contents("C:\path\Need to Pivot.xlsx")),
        Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
        notNull = Table.SelectRows(Sheet1_Sheet, each [Column1] <> null),
        tables = Table.Split(notNull,8),
        transform = List.Transform(tables, each Table.PromoteHeaders(Table.FillDown(Table.Transpose(_),{"Column1"}))),
        rename = List.Transform(transform, each Table.RenameColumns(_, {{Table.ColumnNames(_){0},"Date"}, {"SensorName", "Time"}})),
        combine = Table.Combine(rename),
        types = Table.TransformColumnTypes(combine,{"Time", type time})
    in
        types
    Friday, April 19, 2019 10:57 AM

All replies

  • Hi Success t0rres,

    If shape of your data is always same (8 rows for each date), then you may use following pattern. I guess, performance of this code is not the best and for large datasets you may need another approach.

    let
        Source = Excel.Workbook(File.Contents("C:\path\Need to Pivot.xlsx")),
        Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
        notNull = Table.SelectRows(Sheet1_Sheet, each [Column1] <> null),
        tables = Table.Split(notNull,8),
        transform = List.Transform(tables, each Table.PromoteHeaders(Table.FillDown(Table.Transpose(_),{"Column1"}))),
        rename = List.Transform(transform, each Table.RenameColumns(_, {{Table.ColumnNames(_){0},"Date"}, {"SensorName", "Time"}})),
        combine = Table.Combine(rename),
        types = Table.TransformColumnTypes(combine,{"Time", type time})
    in
        types
    Friday, April 19, 2019 10:57 AM
  • Hey Aleksei,

    Thanks for responding to my question. I am not familiar with M code and I tried your code in my excel but i did not get results. I have attached the snapshot here for you from my screen. Would you please kindly have a look at it once? 

    And, Yes my data is always same 

    Regards,

    Success

    Sunday, April 21, 2019 5:29 AM
  • You need to open the advanced editor, delete all code and then paste the code you were given

    Self Service BI Expert using Power Pivot http://exceleratorbi.com.au

    Sunday, April 21, 2019 6:16 AM
    Answerer
  • Hey Matt, 

    Thanks for the tip. Aleksei's code has given the desired output for my question. 

    Sunday, April 21, 2019 6:41 AM