Forum Discussion
Query with row values
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
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
Hi, my query should work also with multiple "totaling" rows. Have you checked it?
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
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