Transcribe formula DAX into PowerQuery

[Anonymous]

Good afternoon!

I'm having trouble finding an alternative to the following problem:

I'm working with data import through BigQuery and due to the large amount of data it becomes viable to import via DirectQuery and not by import.

However, I need a column that I was able to do only in DAX, through the formula:

liq7diasAntes =

VAR chave7diasAntes = baseVendasSAC[chave7DiasAntes]

RETURN

CALCULATE(SUM(baseVendasSAC[Vlr. Líquido]), FILTER(baseVendasSAC, baseVendasSAC[chaveDia] = chave7diasAntes))

Through DirectQuery it is not possible to add columns via DAX, it does not have the data environment.

Is it possible to do the same function of this formula via PowerQuery or is it somehow accepted via DirectQuery?

Thanks in advance!

[v-eqin-msft · Accepted solution]

Hi @Anonymous ,

Please follow:

Then expand necessary columns:

Bleow is the whole M syntax:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSAWNDpVidaCUnIMsZiI3APBcgyxWIjcE8NyALhE3APJg+U6XYWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [chave7DiasAntes = _t, chaveDia = _t, #"Vlr. Líquido" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"chave7DiasAntes", type text}, {"chaveDia", type text}, {"Vlr. Líquido", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"chave7DiasAntes"}, {{"Count", each _, type table [chave7DiasAntes=nullable text, chaveDia=nullable text, Vlr. Líquido=nullable number, Custom=number]}}),
    #"Added Custom1" = Table.AddColumn(#"Grouped Rows", "Custom", each List.Sum(Table.SelectRows([Count],each [chaveDia]=[chave7DiasAntes])[Vlr. Líquido])),
    #"Expanded Count" = Table.ExpandTableColumn(#"Added Custom1", "Count", {"chave7DiasAntes", "chaveDia", "Vlr. Líquido"}, {"chave7DiasAntes.1", "chaveDia", "Vlr. Líquido"})
in
    #"Expanded Count"

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[wdx223_Daniel]

NewStep=let a=Table.Buffer(Table.Group(baseVendasSAC,"chaveDia",{"amt",each List.Sum([Vlr. Líquido])})) in Table.AddColumn(baseVendasSAC,"liq7diasAntes",each a{[chaveDia=[chave7DiasAntes]]}?[amt]?)