Dynamic column selection

[Jongo]

Hi

I'm trying to sum up reservations from sub locations into geographical locations.

I always end up with errors when updating the model, because one of the sub locations in the model doesn't have any reservations and therefore isn't included in the data.

For instance, if there isn't any reservations on location FI-I-00009, then the model gives an error because it can't find FI-I-00009. How do I code, that the location should be included, if present?

#"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({[#"SE-I-00003"], [#"SE-I-91000"], [#"SE-I-00001"], [#"SE-I-QUARA"], [#"SE-I-90099"], [#"SE-I-90000"]}), type number),
    #"Indsat sum1" = Table.AddColumn(#"Indsat sum", "Sum.1", each List.Sum({[#"FI-I-00009"], [#"FI-I-91000"], [#"FI-I-61000"], [#"FI-I-90000"], [#"FI-I-90099"]}), type number),
    #"Indsat sum2" = Table.AddColumn(#"Indsat sum1", "Sum.2", each List.Sum({[#"DK-I-00009"], [#"DK-I-91000"], [#"DK-I-90000"], [#"DK-I-00011"], [#"DK-I-90099"], [#"DK-I-90098"]}), type number),
    #"Indsat sum3" = Table.AddColumn(#"Indsat sum2", "Sum.3", each List.Sum({[#"DK-I-91100"], [#"DK-I-00021"]}), type number),
    #"Indsat sum4" = Table.AddColumn(#"Indsat sum3", "Sum.4", each List.Sum({[#"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"]}), type number),

Best regards

Jongo

[MarcelBeug]

My suggestion would be to use parameter MissingFields of the Record.ReorderFields function to ignore non-existing columns.

In that code you need to adjust your column references to strings instead of [ColumnName].

An example in the code below that creates a table with 2 columns and tries to add 3 columns.

let
    Source = {1..10},
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Duplicated Column" = Table.DuplicateColumn(#"Converted to Table", "Column1", "Column2"),
    #"Inserted Addition" = Table.AddColumn(#"Duplicated Column", "Inserted Addition", each List.Sum(Record.FieldValues(Record.ReorderFields(_,{"Column1","Column2","Column3"},MissingField.Ignore))), type number)
in
    #"Inserted Addition"

[Jongo]

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"

[MarcelBeug · Accepted solution]

It's partly my fault...

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.

[Jongo]

That did the trick

Thx a lot

[dilkushpatel]

Can't you use grouping feature available on few of the charts?

Also you can create groups and then create measure to sum up columns which are part of group to make it dynamic. I think way you are trying to sum up will hard code things and eventually it will run into issues.