topic Re: Pivot table dates in multiple rows in Power Query

Pivot table dates in multiple rows

Anonymous — Mon, 25 May 2020 09:52:55 GMT

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

Re: Pivot table dates in multiple rows

camargos88 — Mon, 25 May 2020 12:55:40 GMT

Hi Anonymous ,

Can you provide a sample data ?

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

Re: Pivot table dates in multiple rows

dax — Tue, 26 May 2020 05:10:40 GMT

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.

Re: Pivot table dates in multiple rows

Anonymous — Tue, 26 May 2020 07:51:31 GMT

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

Re: Pivot table dates in multiple rows

AlB — Tue, 26 May 2020 09:57:55 GMT

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

Re: Pivot table dates in multiple rows

dax — Tue, 26 May 2020 09:22:55 GMT

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.

Re: Pivot table dates in multiple rows

Anonymous — Tue, 26 May 2020 12:31:26 GMT

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

Re: Pivot table dates in multiple rows

dax — Wed, 27 May 2020 09:06:59 GMT

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.

Re: Pivot table dates in multiple rows

camargos88 — Wed, 27 May 2020 14:09:49 GMT

Hi Anonymous ,

Check if this file helps: Download PBIX

Re: Pivot table dates in multiple rows

Anonymous — Wed, 27 May 2020 15:09:26 GMT

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