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

Jerry Sullivan

MrExcel MVP
Joined
Mar 18, 2010
Messages
8,787
I have this M Code that returns the desired table:

Code:
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

Code:
    #"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!
 
Last edited:

Some videos you may like

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

Ron Coderre

MrExcel MVP
Joined
Jan 14, 2009
Messages
2,316
Maybe something like this?
Code:
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

MrExcel MVP
Joined
Mar 18, 2010
Messages
8,787
Hi Ron,

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

Code:
{"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

Active Member
Joined
Dec 14, 2013
Messages
264
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:
Code:
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
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,109,380
Messages
5,528,366
Members
409,817
Latest member
JiNXX9500

This Week's Hot Topics

  • Change military grades into rank
    Afternoon all Need help with formula that will change military rank (i.e. 1, 2, 3 into Amn, A1C, SrA). Running IF formula that does not work...
  • VBA COUNTIF SOLUTION
    Hi The following are the errors spread across the several columns from E to Q ie. 13 columns across several sheets with more than 500 rows per...
  • INSERT ROW WITH SPECIFIS TEXT IN A COLUMN
    Hi All! How can identify that that the row to be inserted has to be inserted before 1st row with specific text in column F. If I record the...
  • Auto-Create a monthly Sign in sheet for preschool students
    The image below is what each page looks like. Above is space for the "Child Name" "Month" "Class" School days are obviously Monday-Friday but...
  • VBA vlookup multiple results
    Hi folks, Hopefully someone out there can help. I have a list to vlookup which works (ish). the lookup only picks up the first instance of the...
  • Extract values for earliest/latest times
    I am trying to put together a formula to get the earliest start time, the latest end time from column A for each person in Column B-F without the...
Top