Power Query: Using a Text Parameter to Return a [FieldName] List

[Jerry Sullivan]

I have this M Code that returns the desired table:

let
    Source = Table.FromRecords(  
    {  
      [ProjID= "P1", FY19 = 1, FY20 = 1],  
      [ProjID= "P1", FY19 = 11, FY20 = 0],  
      [ProjID= "P2", FY19 = 2, FY20 = 12],  
      [ProjID= "P3", FY19 = 3, FY20 = 13],  
      [ProjID= "P4", FY19 = 4, FY20 = 14]  
    }),

    #"Grouped Rows" = Table.Group(Source, "ProjID", {

        {"Year 1", each List.Sum([COLOR="#FF0000"][FY19][/COLOR]), type number}, 

        {"Year 2", each List.Sum([FY20]), type number}
    })

in
    #"Grouped Rows"

I'd like to replace [FY19] with an expression that returns the same List, using the Text "FY19" which can be replaced with a parameter instead of hard-coding a specific year.

I've tried this syntax but it returns a list of error values

    #"Grouped Rows" = Table.Group(Source, "ProjID", {

        {"Year 1", each List.Sum(
[COLOR="#FF0000"]            Table.SelectColumns(
                Table.SelectRows(Source, _ = _), "FY19")[/COLOR]

    ), type number}, 

        {"Year 2", each List.Sum([FY20]), type number}
    })

Thanks!

 

[Ron Coderre]

Maybe something like this?

let
    Year1Ref = "FY19",
    Year2Ref = "FY20",
    Source = Table.FromRecords(  
    {  
      [ProjID= "P1", Yr1 = 1, Yr2 = 1],  
      [ProjID= "P1", Yr1 = 11, Yr2 = 0],  
      [ProjID= "P2", Yr1 = 2, Yr2 = 12],  
      [ProjID= "P3", Yr1 = 3, Yr2 = 13],  
      [ProjID= "P4", Yr1 = 4, Yr2 = 14]  
    }),
    #"Renamed Columns" = Table.RenameColumns(Source,{{"Yr1", Year1Ref}, {"Yr2", Year2Ref}}),

    #"Grouped Rows" = Table.Group(#"Renamed Columns", "ProjID", {

        {"Year 1", each List.Sum([FY19]), type number}, 

        {"Year 2", each List.Sum([FY20]), type number}
    })

in
    #"Grouped Rows"

Is that something you can work with?

 

[Jerry Sullivan]

Hi Ron,

The part that I haven't been able to solve relates to replacing the expression [FY19] in this line...

{"Year 1", each List.Sum([FY19]), type number},

I'm able to substitute "FY19" with a text parameter that evaluates to "FY19", but I can't use that same syntax where [FY19] is referring a list of values in Column FY19 which is filtered for "each" ProjID.

Your suggestion leads me to see that I could use the workaround of using Column names Year1 and Year 2 throughout the entire query (including the Grouping step), then use RenameColumns as the last step of the query.

I'll use that if I can't find a way to reference the filtered list [FY19] using a Text Parameter.

Since we can return a list using the syntax Table.SelectColumns (tableref, "FY19"), I'm hoping there's a way to use a variation of that as the List argument for each List.Sum([FY19]).

The problem is getting the each keyword to work on that expression, otherwise the entire list is summed for each project.

Thanks for the suggestion!

 

[Ozeroth]

Hi Jerry,
You can use the Table.Column function to refer to a column using its name as text.

In your example, [FY19] is shorthand for _[FY19], and you can get the same result with Table.Column(_,"FY19")

Your sample code would change to:

let
Source = Table.FromRecords(  
    {  
      [ProjID= "P1", FY19 = 1, FY20 = 1],  
      [ProjID= "P1", FY19 = 11, FY20 = 0],  
      [ProjID= "P2", FY19 = 2, FY20 = 12],  
      [ProjID= "P3", FY19 = 3, FY20 = 13],  
      [ProjID= "P4", FY19 = 4, FY20 = 14]  
    }),


    #"Grouped Rows" = Table.Group(Source, "ProjID", {


        {"Year 1", each List.Sum(Table.Column(_,"FY19")), type number}, 


        {"Year 2", each List.Sum(Table.Column(_,"FY20")), type number}
    })


in
    #"Grouped Rows"

Regards,
Owen

 

[Jerry Sullivan]

Hi Owen,

That's perfect. Thanks for clear explanation.