Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Transcribe formula DAX into PowerQuery

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!

 

Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    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

2 Replies

  • wdx223_Daniel's avatar
    wdx223_Daniel
    Community Champion
    4 years ago

    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]?)

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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