Forum Discussion

vpastor's avatar
vpastor
Frequent Visitor
2 years ago
Solved

Query with row values

Hello everyone

 

I am desperate and do not know how I can realize the following:

 

I have two tables:

 

G_L Entry (1 table)

GL Account NoAmount
1700005000 €
2100003500 €
5200001000 €
5202222000 €
1740005000 €
5200008000 €
5500001000 €

 

Acc_ Schedule Line (2 table)

Totaling
170|520|174|5200|5500

 

The totaling field is a text string composed of several values, for example (170|520|174|524|5200|5500).

I need to make a formula that is able to read those values and add the "Amount" when the "GL Account No" starts with one of the values we have read from the "Totaling" column.

 

For example this measure return this:

22.000 €

 

Thank you very much to all of you.

 

Regards
Vicente

 

Need Help
  • dufoq3's avatar
    dufoq3
    2 years ago

    Hi vpastor, check this:

     

    Result

     

    let
    Table1 = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQ3AAIlHSVTIKXwqGmNUqxOtJKRIVTU2BRJ1NQIKmpogCpqZGQEFDVCFjU0N8FiLtwECxRRUwxzYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"GL Account No" = _t, Amount = _t]),
    Table1ChangedType = Table.TransformColumnTypes(Table1,{{"GL Account No", type text},{"Amount", type number}}, "sk-SK"),
    Table1Buffered = Table.Buffer(Table1ChangedType),
    Table2 = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQ3qDE1MqgxNDcB0UCOqYGBUqxOtJKRIVgUzDYEShgaGSvFxgIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Totaling = _t]),
    Ad_SumAmount = Table.AddColumn(Table2, "Sum Amount", each 
        [ a = Text.Split([Totaling], "|"),
          b = Table.SelectRows(Table1Buffered, (x)=> List.Contains(a, x[GL Account No], (y,z)=> Text.StartsWith(z,y)))[Amount],
          c = List.Sum(b) ?? 0
        ][c], type number)
in
    Ad_SumAmount

     

7 Replies

  • dufoq3's avatar
    dufoq3
    Community Champion
    2 years ago

    Hi vpastor, you're welcome 😉

     

    let
    Table1 = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQ3AAIlHSVTIKXwqGmNUqxOtJKRIVTU2BRJ1NQIKmpogCpqZGQEFDVCFjU0N8FiLtwECxRRUwxzYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"GL Account No" = _t, Amount = _t]),
    Table1ChangedType = Table.TransformColumnTypes(Table1,{{"GL Account No", type text},{"Amount", type number}}),
    Table2 = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQ3qDE1MqgxNDcB0UCOqYGBUmwsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Totaling = _t]),
    Table2Split = Table.TransformColumns(Table2, {{"Totaling", each Text.Split(_, "|"), type list}}),
    Table2ToListBuffered = List.Buffer(List.Combine(Table2Split[Totaling])),
    StepBack = Table1ChangedType,
    Ad_Check = Table.AddColumn(StepBack, "Check", each if List.Contains(Table2ToListBuffered, [GL Account No], (x,y)=> Text.StartsWith(y, x)) then 1 else 0, Int64.Type),
    FilteredRows = Table.SelectRows(Ad_Check, each ([Check] = 1)),
    CalculatedSum = List.Sum(FilteredRows[Amount])
in
    CalculatedSum
    • vpastor's avatar
      vpastor
      Frequent Visitor
      2 years ago

      Hi dufoq3 
      Thank you very much for your quick and efficient response.

      I have verified the answer with the example code and it is perfect.
      The problem I have is that table 2 "Acc_ Schedule Line" has not only one row, it is composed of multiple rows. For example:

      Acc_ Schedule Line (2 table)

      Totaling
      170|520|174|5200|5500
      100|123
      70001|70002|70003
      421000|422000


      Could you please modify the query to work with the attached example?


      I suppose we will have to create a column in table 2 that returns the sum of "GL Account No" according to the values of "Totaling".
      Thank you very much

       

      Regards

      Vicente

      • dufoq3's avatar
        dufoq3
        Community Champion
        2 years ago

        Hi, my query should work also with multiple "totaling" rows. Have you checked it?