Help with returning value from the subsequent column in the table, even though the value is BLANK()

[Anonymous]

Greetings everyone. Long story short - I've been stuck with the problem for days now, not knowing what to do, so I've decided to give these forums a go.

First of all, the data (names were altered a little for the sake of ease and confidentiality):
- There are 6 phases, each one has it's own date value.
- Data itself comes from database which I cannot edit; Nor can I edit a SQL-query; so NULLs instead of BLANKs is not an option.

Second, what I need:
- I need the measure to return value of the subsequent column, i.e. measure should return the value of "Start 2" after "Start 1", and that should go on till "Start 6".
- If there is a date value in subsequent column - measure returns it.
- If there is NO date value (i.e. BLANK()) in subsequent column - measure returns "Insufficient Data". (now this one is KEY moment)

Third, what do I get with my measure:
- It does work well when the "Phase" columns are filled and complete:

- It doesn't work well when there are spaces between dates:

          - and THIS IS the problem. Measure I created "jumps over" from "Start 1" straight to "Start 5", ignoring previous column values because them being BLANKs. I tried to preserve it with functions like ISBLANK() and etc., but it jumps over still.

NOTE: The problem not just with "Start 1" and "Start 5" - it can occure with any "Start #" in this range.

[Anonymous]

Hi  @Anonymous ,

Here are the steps you can follow：

1. Create calculated table.

Table 2 =
UNION(
    SELECTCOLUMNS('Table',"Start",[Start1]),
     SELECTCOLUMNS('Table',"Start",[Start2]),
      SELECTCOLUMNS('Table',"Start",[Start3]))

2. Result:

If you need pbix, please click here.

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[amitchandak]

@Anonymous , Check Unpivot in power query can help

https://radacad.com/pivot-and-unpivot-with-power-bi

[Anonymous]

I don't have rights to edit anything in PQ, unfortunately:(