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 ,

You could refer to below M code to see whether it work or not

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fY9LCoAgEEDvMuvAcWz6nKFltBI3iqsO0PVLTcYgBMHnQ31qLVwxnkQwACGhYkXc8NTwDG6wsG/H496RhPe+rAirCiE8qItmzO57KEdNvXxSWpAETa9oUk6jFEo1Sa47f6KjXL8Irs1TsFed32BOS7X8Pc/O3Q==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Column1] <> "" and [Column1] <> "SKU")),
    #"Transposed Table" = Table.Transpose(#"Filtered Rows"),
    #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true]),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Promoted Headers", {}, "Attribute", "Value"),
    #"Added Custom" = Table.AddColumn(#"Unpivoted Columns", "Custom", each if Text.Contains([Attribute], "week") then [Value] else null),
    #"Filled Down" = Table.FillDown(#"Added Custom",{"Custom"}),
    #"Filtered Rows1" = Table.SelectRows(#"Filled Down", each not Text.Contains([Attribute], "week")),
    #"Extracted Text Before Delimiter" = Table.TransformColumns(#"Filtered Rows1", {{"Attribute", each Text.BeforeDelimiter(_, "_"), type text}}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Extracted Text Before Delimiter",{{"Attribute", type text}, {"Value", Int64.Type}, {"Custom", type date}})
in
    #"Changed Type1"

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.

  • AlB's avatar
    AlB
    Community Champion
    6 years ago

    Hi Anonymous

    the file you've attached doesn't show the source data because it is reading from your local folder. Can you paste here the source data (in txt-tabular format, so that it can be copied)? And what is it exactly in dax  approach that is not working??

    Please mark the question solved when done and consider giving kudos if posts are helpful.

    Contact me privately for support with any larger-scale BI needs

    Cheers 

  • 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.