Forum Discussion
Dynamic column selection
- 9 years ago
It's partly my fault... :smileyembarrassed:
My fault: Record.ReorderFields will also select all other fields in your table.
Your fault: you omitted the Record. part of Record.ReorderFields.
Solution: use Record.SelectFields instead of Record.ReorderFields.
Simply adjust all your ReorderFields to Record.SelectFields and keep the rest as is.
I have now tried to implement your suggestion into my code.
It comes up with this error (translated from Danish):
Expression.Error: We cannot use the operator - on the types Text and Text.
Details:
Operator=-
Left=AT-04-872550819-0
Right=AT-04-872550819-0
Can you see what I am doing wrong?
#"Filtrerede rækker1" = Table.SelectRows(#"dbo_VGD$Reservation Entry", each ([Reservation Status] = 0) and ([Source Subtype] = 1)),
#"Grupperede rækker" = Table.Group(#"Filtrerede rækker1", {"Item No_", "Location Code"}, {{"Quantity (Base)", each List.Sum([#"Quantity (Base)"]), type number}}),
#"Filtrerede rækker" = Table.SelectRows(#"Grupperede rækker", each ([Location Code] <> "DK-I-00014" and [Location Code] <> "DK-P-00009" and [Location Code] <> "DK-P-00010" and [Location Code] <> "DK-P-00012" and [Location Code] <> "SE-I-00004" and [Location Code] <> "SE-P-00004" and [Location Code] <> "SE-P-00022")),
Pivotkolonne = Table.Pivot(#"Filtrerede rækker", List.Distinct(#"Filtrerede rækker"[#"Location Code"]), "Location Code", "Quantity (Base)", List.Sum),
#"Indsat sum" = Table.AddColumn(Pivotkolonne, "Sum", each List.Sum(Record.FieldValues(ReorderFields(_,{"SE-I-00003", "SE-I-91000", "SE-I-00001", "SE-I-QUARA", "SE-I-90099", "SE-I-90000"},MissingField.Ignore))), type number),
#"Indsat sum1" = Table.AddColumn(#"Indsat sum", "Sum.1", each List.Sum(Record.FieldValues(ReorderFields(_,{"FI-I-00009", "FI-I-91000", "FI-I-61000", "FI-I-90000", "FI-I-90099"},MissingField.Ignore))), type number),
#"Indsat sum2" = Table.AddColumn(#"Indsat sum1", "Sum.2", each List.Sum(Record.FieldValues(ReorderFields(_,{"DK-I-00009", "DK-I-91000", "DK-I-90000", "DK-I-00011", "DK-I-90099", "DK-I-90098"},MissingField.Ignore))), type number),
#"Indsat sum3" = Table.AddColumn(#"Indsat sum2", "Sum.3", each List.Sum(Record.FieldValues(ReorderFields(_,{"DK-I-91100", "DK-I-00021"},MissingField.Ignore))), type number),
#"Indsat sum4" = Table.AddColumn(#"Indsat sum3", "Reservations", each List.Sum(Record.FieldValues(ReorderFields(_,{"SE-I-00003", "DK-I-00009", "DK-I-91000", "FI-I-00009", "DK-I-90000", "DK-I-00011", "DK-I-90099", "DK-I-90098", "DK-I-91100", "SE-I-91000", "DK-I-00021", "FI-I-91000", "SE-I-00001", "FI-I-61000", "FI-I-90000", "FI-I-90099", "SE-I-QUARA", "SE-I-90099", "SE-I-90000"},MissingField.Ignore))), type number)
in
#"Indsat sum4"It's partly my fault... :smileyembarrassed:
My fault: Record.ReorderFields will also select all other fields in your table.
Your fault: you omitted the Record. part of Record.ReorderFields.
Solution: use Record.SelectFields instead of Record.ReorderFields.
Simply adjust all your ReorderFields to Record.SelectFields and keep the rest as is.
- Jongo9 years agoNew Member
That did the trick
Thx a lot