Change the data type after getting the cell value from a row below with index

[Anonymous]

Hello everyone

I have the following problem:

Sample Table:

Column	Text	Index
Date	11.02.2022	1
Message	Accident	2
Date	09.02.2022	3
Message	Machine stop	4

In the sample table, you see 2 different types of row: Date or Message

My Goal is to get the Text cell from the Message-Row on the same row as the date

11.02.2022	Accident
09.02.2022	Machine stop

So I made a custom column using the index:

=if [Index] > 0 then Tabelle1[Text]{[Index]} else 0

Now if I want to change the datatype from my Text column so to date, so I can have only date values, I get this error: Expression.Error: A cyclic reference was detected during evaluation.

Can someone help me?

Or you may have a better idea to get the cell value?

Thank you

[serpiva64 · Accepted solution]

Hi, 

to achieve this

you need:

- pivot column

- fill up Message

- deselect null in column Date

- remove index

that's done

If this post is useful to help you to solve your issue consider giving the post a thumbs up 👍

 and accepting it as a solution !