How to Use a Dynamic Named Range Variable in Power Query Grouped Rows Function for Automation

Question

I have a key workbook that is linked to many other workbooks through PQ and I wanted to update the data I pull from these external workbooks based on a dynamic named named range.

I have called the named range 'QtrDate'. Is it possible to update the M code in PQ to incorporate the named range 'QtrDate' into the following;

= Table.Group(#"Removed Columns", {"Deal", "Financial Statement Caption (FSC)", "FSC Alternative/Sub-Caption", "FS Location", "Gross v Ceded", "Account Number", "Account Description"}, {{"9/30/2023", each List.Sum([#"9/30/2023"]), type nullable number}})

I want the column name 9/30/2023 to now = QtrDate and the List.Sum to = QtrDate.

The QtrDate named range in this instance is = 9/30/2023

I tried replacing {"9/30/2023",... with QtrDate but PQ gives the following error:

Expression.Error: We cannot convert the value #date(2023, 9, 30) to type Text. Details: Value=9/30/2023 Type=[Type]

horseyride · Accepted Answer · 2023-09-15 17:50:44Z

2

in the workbook, create a single cell named range QtrDate that contains a date formatted as a date (no leading single or double apostrophe)

In powerquery pull that value and use it

QtrDate= Excel.CurrentWorkbook(){[Name="QtrDate"]}[Content]{0}[Column1],
DateText=Date.ToText(Date.From(QtrDate)),
part3 = Table.Group(#"Removed Columns", {"Deal", "Financial Statement Caption (FSC)", "FSC Alternative/Sub-Caption", "FS Location", "Gross v Ceded", "Account Number", "Account Description"}, {{DateText, each List.Sum(Table.Column(_,DateText)), type nullable number}}),

edited Sep 15, 2023 at 17:50

answered Sep 15, 2023 at 17:45

horseyride

21.8k3 gold badges15 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to Use a Dynamic Named Range Variable in Power Query Grouped Rows Function for Automation

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related