Forum Discussion
Create Calculated Table and replacing empty values, with the "type"
Hello,
unfortunately I got a table which is suboptimal. Here is my sample table:
| OrderNo. | Date | Type | Materials | |||
| 11111 | 01.01.2022 | null | Felo | |||
| 11111 | 01.01.2022 | null | Olef | |||
| 11111 | 01.01.2022 | null | 5X | |||
| 11111 | 01.01.2022 | Freight | ShipSquare | |||
| 22222 | 01.01.2022 | null | Rolle | |||
| 22222 | 01.01.2022 | null | YER | |||
| 22222 | 01.01.2022 | null | 54C | |||
| 22222 | 01.01.2022 | Box | 4Square |
As you can see in the column "Type" I have many emply or null values. In that case I need a calculated column which always replace all null/empy values with the type which is at least in on of those rows in each ordernumber.
For example ordernumber 11111 has one row in "Type" which is not empty: "Freight", and also ordernumber 22222 has one row which is not empty : "Box".
That is the outcome I intend:
| OrderNo. | Date | Type | Materials | CalculatedColumn | ||||
| 11111 | 01.01.2022 | null | Felo | Freight | ||||
| 11111 | 01.01.2022 | null | Olef | Freight | ||||
| 11111 | 01.01.2022 | null | 5X | Freight | ||||
| 11111 | 01.01.2022 | Freight | ShipSquare | Freight | ||||
| 22222 | 01.01.2022 | null | Rolle | Box | ||||
| 22222 | 01.01.2022 | null | YER | Box | ||||
| 22222 | 01.01.2022 | null | 54C | Box | ||||
| 22222 | 01.01.2022 | Box | 4Square | Box |
Thank you very much in advance.
Best.
Applicable88 In DAX you could do this:
Column = VAR __OrderNo = [OrderNo.] VAR __Type = MAXX(FILTER('Table',[OrderNo.] = __OrderNo),[Type]) RETURN __Type
3 Replies
Applicable88 In Power Query Editor, use the Fill Up feature. Right-click column header, Fill | Up
Greg_Deckler , unfortunately not possible.
Because there are indeed OrderNo. with no Type at all, where empt/null should not be filled up or down. any ideas in DAX? If its a solution in PQ its also good.
Applicable88 In DAX you could do this:
Column = VAR __OrderNo = [OrderNo.] VAR __Type = MAXX(FILTER('Table',[OrderNo.] = __OrderNo),[Type]) RETURN __Type
