Where to write the "section-document" in Excel using power query?

Question

I can write "expression-document" in advance editor. But putting the "section-document" will cause error.

According to PDFPower Query for Excel Formula Language Specification, Aug 2015, Page 15, 2.1 Documents

A document either consists of exactly one expression or of groups of definitions organized into sections.

When I put "section definition" like following, in "Advance Editor", the error message is "Invalid Expression":

section SectionA;
A = 1;

It is quite obviously that the Advance Editor expecting "expression-document", not "section-document". (These 2 terms come from PDF page 107, 12.2.1 Document. And "expression-document" is just an "expression", 12.2.3.1 Expressions)

I sense that "Section" in PQ would be a feature to organize code as some way like "Module". I would like to use it.

However, I find no way to code "section-document". (page 98, 11.Sections of the spec)

A work-a-round is to create a "Query" named as said, MyFunSet, which returns a record of many functions.

Query-Name: MyFunSet has following "expression" in Adv Editor

[ FunA = () => ...,
   FunB = (x) => ...
]

When using this MyFunSet in other "Query" would be like following. I have tested that it works.

  let a = MyFunSet[FunA]()
  ,   b = MyFunSet[FunB](1)
  in  1

or

let FunA = MyFunSet[FunA]
,   FunB = MyFunSet[FunB]
,   a = FunA()
,   b = FunB(1)
in  1

This work-a-round is OK, but not nice.

Finding#1 (#settions) PDF page 100, PQ expression #sections will return a record of sections. Create a new query of value #sections, the record returned has ONE field of name 'Section1' and value is a record of all queries in the Excel workbook. Eg, A workbook has

Query1 = ...
Query2 = ...
Query3 = #sections

Query3 will return a record like this:

[ Section1 = [Query1 = ..., Query2 = ..., Query3 = ...] ]

According to this, I interpret that whole Excel workbook only allow one section and the name is "Section1"

Finding#2 (section-access expression) PDF page 110,

12.2.3.13 Section-access expression
   section-access-expression: identifier ! identifier

Finding#3 (Document) PDF page 107,

12.2.1 Documents
    document: 
        section-document
        expression-document

"Advanced Editor" accepts only expression-document, not section-document

Finding#4 (Export the PQ as *.odc)

In Excel 356, each loaded PQ is a workbook connection and can exported as *.odc

• Locate the cell within the loaded query table,
• go menu "Data", click "Connection Properties..",
• in page tab, "Definiton", click the "Export Connection File",
• a *.odc is created.
• examine the tag in the *.odc file

following is find

<Formula><![CDATA[let
Query3 = #sections,
in
Query3]]></Formula>

These 4 findings make me feel that one workbook will always create only one "section" of name "section1". If an workbook can "refer" to one or more other workbook, multiple-section is allowed. These comes with 2 questions:

• How to refer?
• How to rename the section-name which is always "Section1" to other name?

I would like to know how to use -- "section" in Excel Power Query.

Answer 1

Great question. You can't edit the section document directly. In Excel/Power Query each time you edit the expression of some Query you're writing a part of the section document.

If you define "Query1" as 1 + 1 and create a separate "Query2" as Query1 + 1, then Excel writes out this section document:

section Section1;

shared Query1 = 1 + 1;

shared Query2 = Query1 + 1;

Your workaround should also work fine. I think that's what e.g. this "M" library on GitHub does.