Reshaping Dataset with Power Query

[Danielnir]

Hi All,

I have a dataset with all the downtime information. Every row contains information from a paper document used by production staff. Because of that, each downtime event is stored under a separate column. What I need to achieve is to store all that data under a unified set of columns. I'm attaching a file and pictures to show exactly what needs to be done. I want to use Power Query for that as the dataset is updated daily and I would like to schedule an automatic monthly refresh.

Current Shape:

Desired Shape:

I tried duplicating the original dataset and leaving just the next series of columns and then appending them all but the performance was terrible and I ended up with lots of tables. I'm looking for a better solution. 

May I ask for your help?

[serpiva64]

Hi,

Youcan try this:

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bY9BCoAwDAT/0rPQJEXBoxZFQcGDN/H/3zCbWhErlCWks5vkOByxp+CFRFzldlZpBxUhVD2qWgXvrD40YAHMQGRUCbCFLvUKQ4pf8GuWNuZ8m9T8WODgCaHAeAZmM2KqzCDFjGdxO8a2ek5405vl06pa5ztv9LwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Line = _t, DK = _t, PI = _t, DK1 = _t, PI1 = _t, DK2 = _t, PI2 = _t]),
#"Added Index" = Table.AddIndexColumn(Source, "Index", 1, 1, Int64.Type),
#"Reordered Columns" = Table.ReorderColumns(#"Added Index",{"Index", "Date", "Line", "DK", "PI", "DK1", "PI1", "DK2", "PI2"}),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Reordered Columns", {"Index","Date", "Line"}, "Attribute", "Value"),
#"Replaced Value" = Table.ReplaceValue(#"Unpivoted Other Columns","1","",Replacer.ReplaceText,{"Attribute"}),
#"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","2","",Replacer.ReplaceText,{"Attribute"}),
#"Grouped Rows" = Table.Group(#"Replaced Value1", {"Index", "Date", "Line", "Attribute"}, {{"Count", each _, type table [Index=number, Date=nullable text, Line=nullable text, Attribute=nullable text, Value=text]}}),
#"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([Count],"Index1",1)),
#"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Custom"}),
#"Expanded Custom" = Table.ExpandTableColumn(#"Removed Other Columns", "Custom", {"Index", "Date", "Line", "Attribute", "Value", "Index1"}, {"Index", "Date", "Line", "Attribute", "Value", "Index1"}),
#"Pivoted Column" = Table.Pivot(#"Expanded Custom", List.Distinct(#"Expanded Custom"[Attribute]), "Attribute", "Value")
in
#"Pivoted Column"

and you will obtain this

If this post is useful to help you to solve your issue consider giving the post a thumbs up 

 and accepting it as a solution !

[Danielnir]

Hi @serpiva64 

thanks fo your reply. Code looks great but I rather need to understand how to do it myself not to ask such questions in the future. Your solution would probably work but I don't have such advanced knowledge to adjust it by myself. 

[serpiva64 · Accepted solution]

If it's better for you in order to adapt the solution to your necessity here are the steps i used:

- first add a index column

- reorder it to the first column

- select the first 3 column and unpivot other columns

- now you need to replace characters 1 and 2 in order to have a unique DK and PI

- group rows

- add a new index column inside your column of tables

-remove all columns beside the last one

- expand all

- and then finally pivot it again

[Danielnir]

Hi, thanks for your reply!

I have the same problem as with the other solution (although I have more cells populated). 

[Danielnir]

In reality, this data set contains something like 12 or 13 such sets of columns. I managed to prepare them all like you suggested but am having problems as described.

[serpiva64]

I don't know why, but until i did no try to add the second index column before pivoting again i always got the same error you had 

[Danielnir]

Hi @serpiva64! It finally worked! The only additional step I had to make is to move the second index column before the attribute column. Worked like a charm!

Thanks a lot!

Daniel

[serpiva64]

i know that the reality is always more complex that sample data.

I think the problem is not in the number of set of data (i tried duplicating the data and it function)  

[Danielnir]

Hi, thanks for your reply! 

I tried it but didn't get the expected result. The problem lies in those "Downtime Key" columns. There are lot's of them due to the fact that each one is recording another downtime event that occurred during a specific job. 

Your solution would work for just one column "Downtime Key" and others just with numerical values. (Of course only if I got that right).

Kind regards,

Daniel Kwiatkowski

[serpiva64]

Sorry if i mistake, 

the table i used is like the one you provided and in it there are more then one DowntimeKey

so i think that there is another problem

[amitchandak]

@Danielnir ,  the set of three columns can not have same names.

So first Unpivot other than first column.

Then make sure names are the same first three and last three columns set in rows- Use replace - or split column

then pivot again using the value column created during the unpivot

https://radacad.com/pivot-and-unpivot-with-power-bi

If this does not help
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

[Danielnir]

Hi @amitchandak 

thanks for your reply. Finally! A different error message! It's progress, right? 

After following your instructions, I managed to get the proper number of columns. Unfortunately, they are mostly showing errors. 

I selected only the attribute column and also ticked "Don't Aggregate".

Anything I missed?

Kind regards,

Daniel