Forum Discussion
Dynamic column choice if null for index needed
Dear colleagues, I want to define an index in a column. This index should be next available record in one of the columns. For each quarter there is column, the index column should show the most earliest entry which is available. Any tricks on that one?
So e.g. line 216 should show the price from Q3
given the limited information you provide it is not possible to provide a suitable answer for general cases. I give you an idea of what can be done, based on what I understand for what you ask. The answer can be improved, but you must also collaborate, providing more precise information on the structure of your data and loading tables that can be copied: one the starting one and the other desired.
You can use the null coalescing operator:
Table.AddColumn(TableName, "Index", each [Column1]??[Column2]??[Column3]??[Column4]??"Default Value")
Which is if Column1 =null then Column2 unless Column 2 is null then Column3 unless Column3 is null then Column4 unless Column4 is null then use Default Value.
--Nate
6 Replies
Hello, maybe the easier way would be to unpivot (Transform >> Unpivot) the Yr/Qtr columns? You can then run any DAX calculations over just a single values column.
Unfortunatley this view is needed for the next step
Hi lennardheyder ,
So you also want the line 227 to show the price from Q1?
If not,pls provide some sample data in editable format with expected output.
Much appreciated.
Best Regards,
KellyDid I answer your question? Mark my post as a solution!
given the limited information you provide it is not possible to provide a suitable answer for general cases. I give you an idea of what can be done, based on what I understand for what you ask. The answer can be improved, but you must also collaborate, providing more precise information on the structure of your data and loading tables that can be copied: one the starting one and the other desired.
You can use the null coalescing operator:
Table.AddColumn(TableName, "Index", each [Column1]??[Column2]??[Column3]??[Column4]??"Default Value")
Which is if Column1 =null then Column2 unless Column 2 is null then Column3 unless Column3 is null then Column4 unless Column4 is null then use Default Value.
--Nate
