Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Pivot table dates in multiple rows

Hi all, I have an excell query that look like this example:   I am tyring to figure  out how to transform data so that the table looked something like this: SKU Date Value AAA 25/0...
Need Help
dax's avatar
dax
Community Support
6 years ago

Hi Anonymous , 

Yes, as AlB  mentioned, I think you need to upload your excel file, because when I view original data in Power Query, it can't show correctly (it will prompt error of can't find excel path). You could directly copy Excel content and paste this  in forum or you also could try to upload virtual data, then I could try to reproduce your problem.

Please do mask sensitive data before uploading.

Thanks for your understanding and support.
Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hello,

     

    I thought that the data was visible in the pbix file, sorry for that

     

    The thing is that I simplified my original table to facilitate the job and to make it easier to understand what I needed to do. So when I went back to my original table, with a couple of columns more, I dindnt know how to replicate the M code. Here is my original table:

     

    https://www.dropbox.com/scl/fi/hgl1vgvmwgqc1sz8tqpdp/Forecast2.xlsx?dl=0&rlkey=irehvx3a78r0okct0x8mzm647

     

    Just to give a little more details, the table is a weekly production forecast, where

    Line1, Line2 = Production Lines

    SKU : Item

    Description: Item description -> It can be omited from the table

    Form: weight of the item - > It can be omited from the table

    And for each day, the expected production quantity

    I hope it help

    Thank you all for helping me

    Regards

     

     

    • dax's avatar
      dax
      Community Support
      6 years ago

      Hi Anonymous ,

      I think it will be difficult to modify multiple tables in the same sheet, you could try save them in the different sheets or convert them into pivot tables(make sure they have the same column name, type and data structure ), the you could invoke function to transform it easily in M code. Below is my sample and I change something in  your sample. You also could refer to https://blog.crossjoin.co.uk/2018/07/09/power-bi-combine-multiple-excel-worksheets/  for details.

      Best Regards,
      Zoe Zhi

      If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • camargos88's avatar
      camargos88
      Community Champion
      6 years ago

      Hi Anonymous ,

       

      Check if this file helps: Download PBIX 

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        Thanks camargos88 !

        Awsome solution! besides I have learnt how to use Table.Group function. Thanks a lot.

         

        Thank you very much dax too for your approach. I has been very useful!

         

        Many thanks both!!