Forum Discussion
CREATE COLUMN THAT REFERENCES SPECIFIC CELL
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?
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
= ChangedType3. Add a Custom Column which simply points to Custom1
= Table.AddColumn(Custom2, "Date", each Custom1)
3 Replies
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
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
= ChangedType3. Add a Custom Column which simply points to Custom1
= Table.AddColumn(Custom2, "Date", each Custom1)
