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
dufoq3
Community Champion
Community ChampionHi 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
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?
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
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