Search code examples
automationpowerquerynamed-ranges

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

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]

Solution

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

    enter image description here

    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}}),