data-structurespowerquerydata-analysismdummy-variable

Scaleable M code to create n number of columns based on the unique values in a column?


I have a large dataset filled with customer purchase data. There are two columns that are of specific interest to me: the date of a purchase and the order of the purchase (1 means it was the customer's first purchase, 2 means it was the customer's second purchase, and so on).

I would like to create a column for each unique purchase order value, and fill it with the corresponding date.

The end result would be n number of columns named "1st purchase date", "2nd purchase date", [...], "n purchase date" for each customer.

This is what the table looks like currently:

enter image description here

This is a sample of the desired output:

enter image description here

Apologies if the table formatting is unclear.

I don't want to do this manually, as the purchase order variable is variable. I am however conscious of the fact that there might be too many columns in the end, so if it's possible to add a limit to the number of columns, that would be fantastic.

Thanks in advance!

I tried to do this manually, but it's arduous and limiting (the max value of the purchase order is currently >60).


Solution

  • In powerquery, click select the Purchase Order Column

    Transform ... pivot column...

    for Values Column choose Date, and in Advanced Options pick Don't aggregate

    enter image description here

    enter image description here