Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Jongo
New Member

Dynamic column selection

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

1 ACCEPTED SOLUTION
MarcelBeug
Community Champion
Community Champion

It's partly my fault... Smiley Embarassed

 

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.

Specializing in Power Query Formula Language (M)

View solution in original post

5 REPLIES 5
MarcelBeug
Community Champion
Community Champion

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"

 

Specializing in Power Query Formula Language (M)

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
Community Champion
Community Champion

It's partly my fault... Smiley Embarassed

 

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.

Specializing in Power Query Formula Language (M)

That did the trick

 

Thx a lot

dilkushpatel
Helper III
Helper III

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.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors