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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.