Pivot table dates in multiple rows

[Anonymous]

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/05/2020	100

any help on this is really appreciated

Reuben

[dax]

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.

[Anonymous]

thanks @dax  for you help

I have tried to replicate your M code in my model, but it seems that I'm missing a step somewhere that makes not working.

https://www.dropbox.com/s/f00wqiizkmerat9/Forecast.pbix?dl=0 

thank you very much

Reuben

[dax]

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]

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=irehvx3a78r0okct0x8mz...

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

[camargos88 · Accepted solution]

Hi @Anonymous ,

Check if this file helps: Download PBIX 

[Anonymous]

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!!

[dax]

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.

[AlB]

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 

[camargos88]

Hi @Anonymous ,

Can you provide a sample data ?

We can create a m-code and share it with you.