Forum Discussion
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 No Amount 170000 5000 € 210000 3500 € 5200...
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, 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