Forum Discussion
Helper IIReshaping Dataset with Power Query
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?
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
12 Replies
- amitchandak
Super User
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
- 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
Solution Sage
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
- 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 !
- serpiva64
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
