DAX Calculated Column needed in Query for join

[BarbaraK]

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!

[parry2k · Accepted solution]

@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"

[BarbaraK]

Oh that is beautiful.  I'm pretty sure I've got it from here. 🙂

[parry2k]

@BarbaraK that's awesome, glad to hear. Cheers!!

[BarbaraK]

At the risk of sounding daft:  This is my screen ATM and I am not really sure where to from here?

[parry2k]

@BarbaraK right click on query1, and in pop up menu there is an option advanced editor , choose that and that is where you paste the code, make sure remove existing code