Query with row values

[vpastor]

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 No	Amount
170000	5000 €
210000	3500 €
520000	1000 €
520222	2000 €
174000	5000 €
520000	8000 €
550000	1000 €

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

[dufoq3]

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]

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]

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

[vpastor]

Hi @dufoq3,

Yes, your query work corretly, but i need this result for example:

G_L Entry (1 table)

GL Account No	Amount
170000	5000 €
210000	3500 €
520000	1000 €
520222	2000 €
174000	5000 €
520000	8000 €
550000	1000 €

Acc_ Schedule Line (2 table)

Totaling	Sum Amount
170|520|174|5200|5500	22.000 €
210|174	8500 €
100|123	0 €

I would be very grateful if you could adapt the query to my needs.

Thank you so much!

Vicente

[dufoq3 · Accepted solution]

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

[vpastor]

Hi @dufoq3,

I'm afraid not, when I copy your answer it does not work correctly.

This is the error:

Thank you so much!

[dufoq3]

I've edited code above. Try it now.