Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
Solved! Go to Solution.
@Applicable88 In DAX you could do this:
Column =
VAR __OrderNo = [OrderNo.]
VAR __Type = MAXX(FILTER('Table',[OrderNo.] = __OrderNo),[Type])
RETURN
__Type
@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
User | Count |
---|---|
93 | |
83 | |
77 | |
72 | |
65 |
User | Count |
---|---|
114 | |
101 | |
96 | |
65 | |
60 |