CREATE COLUMN THAT REFERENCES SPECIFIC CELL

[Anonymous]

I have imported a crazy excel file into PBI which I need to model. Column 1 Row 1 holds a date, the headers are all on Row 2.

So I want to create a [Date] column which points to Column 1 Row 1 and then I can promote headers. 

How do I do this?

[johnt75]

Have a look at https://community.powerbi.com/t5/Desktop/How-to-move-only-in-row-to-be-a-column/m-p/2421430 

[amitchandak]

@Anonymous , Take rows 1 and column into a different table. Delete everything. In another table keep everything other than date 1 and column. In both tables add a column with the same value and merge these two tables using that

[Anonymous · Accepted solution]

I figured it out.

1. Insert a step (Custom1) to pull your date

= Record.Field(ChangedType{0},"Column1")

2. Inset a step (Custom2) to return your table by pointing to the step before Custom1

= ChangedType

 3. Add a Custom Column which simply points to Custom1

= Table.AddColumn(Custom2, "Date", each Custom1)