Forum Discussion
DAX Calculated Column needed in Query for join
I have query data "Weekly Sales" that looks a bit like this (changed for confidentiality):
| Code | Desc | Qty | Index |
| Debtor: XYZ Debtor | 1 | ||
| 1010 | Product 1 | 12 | 2 |
| 1020 | Product 2 | 3 | 3 |
| 1030 | Product 3 | 4 | 4 |
| Debtor: PRT Debtor | 5 | ||
| 1010 | Product 1 | 44 | 6 |
| 1020 | Product 2 | 66 | 7 |
| 1030 | Product 3 | 3 | 8 |
Using this DAX:
Location = VAR a = 'Weekly Sales'[Index]
RETURN
CALCULATE (MAX ( 'Weekly Sales'[Desc] ),FILTER (ALL ( 'Weekly Sales' ),'Weekly Sales'[Index]= CALCULATE (MAX ( 'Weekly Sales'[Index] ),FILTER ( ALL ( 'Weekly Sales' ), 'Weekly Sales'[Index] <= a && LEFT('Weekly Sales'[Desc],6) = "Debtor" ))))
I get a calculated column that looks like this:
| Location | Code | Desc | Qty | Index |
| Debtor: XYZ Debtor | Debtor: XYZ Debtor | 1 | ||
| Debtor: XYZ Debtor | 1010 | Product 1 | 12 | 2 |
| Debtor: XYZ Debtor | 1020 | Product 2 | 3 | 3 |
| Debtor: XYZ Debtor | 1030 | Product 3 | 4 | 4 |
| Debtor: PRT Debtor | Debtor: PRT Debtor | 5 | ||
| Debtor: PRT Debtor | 1010 | Product 1 | 44 | 6 |
| Debtor: PRT Debtor | 1020 | Product 2 | 66 | 7 |
| Debtor: PRT Debtor | 1030 | Product 3 | 3 | 8 |
This is exactly what I need but I need it in the query not on the destop so that I can use it in a query join. Any help on how I can do this in query M or in the query editor would be very appreciated!
BarbaraK in query editor start a blank query and paste the following code and you will see the step on how to create one
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WUtJRcklNKskvslKIiIxSgLCBgkBkqBSrE61kaGBoAOQEFOWnlCaXKBiCJIyAhBFU1ghZFiRhDMYQSWNkSZCECRiDJJEsDggKQbHYFJfFJiDtZrgsNjMDEua4bAZhC6XYWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Code = _t, Desc = _t, Qty = _t, Index = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Code", Int64.Type}, {"Desc", type text}, {"Qty", Int64.Type}, {"Index", Int64.Type}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Location", each if [Code] = null then [Desc] else null), #"Filled Down" = Table.FillDown(#"Added Custom",{"Location"}) in #"Filled Down"
5 Replies
BarbaraK in query editor start a blank query and paste the following code and you will see the step on how to create one
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WUtJRcklNKskvslKIiIxSgLCBgkBkqBSrE61kaGBoAOQEFOWnlCaXKBiCJIyAhBFU1ghZFiRhDMYQSWNkSZCECRiDJJEsDggKQbHYFJfFJiDtZrgsNjMDEua4bAZhC6XYWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Code = _t, Desc = _t, Qty = _t, Index = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Code", Int64.Type}, {"Desc", type text}, {"Qty", Int64.Type}, {"Index", Int64.Type}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Location", each if [Code] = null then [Desc] else null), #"Filled Down" = Table.FillDown(#"Added Custom",{"Location"}) in #"Filled Down"At the risk of sounding daft: This is my screen ATM and I am not really sure where to from here?
Oh that is beautiful. I'm pretty sure I've got it from here. 🙂
