Forum Discussion
Change data set structure, unsure how to transform this
I have a dataset that isn't structured ideally. I'm hoping someone can suggest a way to transform this.
Current structure has different Gate purpose dates across in columns.
Col1text-ProjectName, Col2date-GateA, Col3date-GateB, Col3date-GateC
Eg.,
ProjectF, Jan4, Mar6, Jun10
ProjectH, Feb8, Jul12, Aug15
Etc
I would prefer it tabular like this:
Col1text-ProjectName, Col2text-GateType, Col3date-GateDate
Eg.,
ProjectF,GateA,Jan4
ProjectF,GateB,Mar6
ProjectF,GateC,Jun10
ProjectH,GateA,Feb8
Etc
Any help would be greatly appreciated. Thank you
It could be done with Power Query
please see in attached file.
If with formulae it depends on which Excel platform/version you are and how big the source data.
5 Replies
That appear to be a classic 'unpivot' operation and the best point at which to apply it is often within PowerQuery before the data is ever loaded to Excel. Now however, Excel itself is reasonably capable when it comes to such restructuring (normalisation) processes. The basic function to apply is TOCOL
= TOCOL(date)but, assuming you also want the row and column headers to appear, this becomes
= LET( p, IF(date, project), g, IF(date, gate), HSTACK(TOCOL(p), TOCOL(g), TOCOL(date)) )where the formula assumed 'date' to be datevalues and contain no blanks. If there are blanks, the next step is
= LET( p, IF(date, project, NA()), g, IF(date, gate, NA()), HSTACK(TOCOL(p,3), TOCOL(g,3), TOCOL(date,1)) )As an aside, to reverse the process using the insider version of 365
= LET( project, CHOOSECOLS(unpivotted, 1), gate, CHOOSECOLS(unpivotted, 2), date, CHOOSECOLS(unpivotted, 3), PIVOTBY(project, gate, date, MAX, , 0, , 0) )It could be done with Power Query
please see in attached file.
If with formulae it depends on which Excel platform/version you are and how big the source data.
SergeiBaklan thanks for the suggestion! Your sample file was helpful. While I was in Power Query, I noticed the Unpivot Columns transformation feature and it worked as well. I selected the 3 date columns to Unpivot and it transforms the table into desired tabular data. 2 solution options if anyone else has this use case. Thanks.
Oops, I misunderstood how source data is organized. If from first table to second
yes, that's two-step Power Query script
let Source = Excel.CurrentWorkbook(){[Name="data"]}[Content] in Table.UnpivotOtherColumns(Source, {"Project"}, "Gate", "Date")
What you propose makes sense in a very general way. Your request for help, though, is hard to meet or satisfy without knowing a bit more. What is the whole database about? You've touched ever so briefly on what might be called the INPUT end of things, and how you want that initial raw data to be stored. What is the desired OUTPUT? Is there any intermediate PROCESS--anything that happens to the initial raw data before we reach the OUTPUT end? And so forth.
In short, you could help us help you by giving more of the big picture.
