SOLVED

Change data set structure, unsure how to transform this

Copper Contributor

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

5 Replies

@AllanT1495 

 

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.

best response confirmed by AllanT1495 (Copper Contributor)
Solution

@AllanT1495 

It could be done with Power Query

image.png

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.  

@AllanT1495 

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

 

 

 

@AllanT1495 

Oops, I misunderstood how source data is organized. If from first table to second

image.png

yes, that's two-step Power Query script

let
    Source = Excel.CurrentWorkbook(){[Name="data"]}[Content]
in
    Table.UnpivotOtherColumns(Source, {"Project"}, "Gate", "Date")

 

1 best response

Accepted Solutions
best response confirmed by AllanT1495 (Copper Contributor)
Solution

@AllanT1495 

It could be done with Power Query

image.png

please see in attached file.

If with formulae it depends on which Excel platform/version you are and how big the source data.

View solution in original post