cancel
Showing results for 
Search instead for 
Did you mean: 
V-lianl-msft

Nested M query functions to apply calculation into other function results or variables

Scenario: 

The basic navigation panel used by M query function can calculate with query table records and add different query steps into the query table. They were not suitable to handle some complex scenarios(e.g. apply some advanced operations with its result or interaction with its calculations contents).  

In this article, I would like to share some tips on nested functions to handle some general scenarios where some basic functions don’t work.

Usage scenario:

Nested data connects to expand and recognize the files from list or online sites.(SharePoint, folder)

Sample data:

V-lianl-msft_0-1619073468979.png

 

1. Check content file to confirm detail file type to choose the correspond data connector. (For this sample, they are excel files so I choose excel connector)

2. Click on one row to ensure the internal structure. (The source table is stored in the recognized table 'data' field, we can add [field name] after function to pick up specific field values) 

#"Imported Excel" = Excel.Workbook('file path or binary data')[Data]

V-lianl-msft_1-1619073533456.png

 

3. Add a custom column step after 'source' steps with excel connector to recognize binary data.

#"Added Custom" = Table.AddColumn(Source, "Custom", each Excel.Workbook([Content])[Data])

V-lianl-msft_2-1619073802994.png

 

4. Expand the new field and we can see the internal table has been recognized correctly. 

V-lianl-msft_3-1619073834026.png

 

5. Expand content and remove other columns to get the combine table records. (expand and rename columns)

 #"Expanded Custom1" = Table.ExpandTableColumn(#"Removed Other Columns","Custom",{"Column1","Column2"},{"ID","Name"})

V-lianl-msft_4-1619073870150.png

 

Since these tables are imported directly, 'data shaping' steps have not been applied to these internal tables. (e.g. filter rows, change column data types) 

For this scenario, we can use the function to package these and looping these content to apply custom operations.

Since [filed name] will extract the list value, we can choose List.Transform which includes iterator to loop its content and add process. (here I nested 'FirstN' and 'LastN' to keeping median part rows of raw table records, remove first 3 and bottom 2 rows)

    #"Added Custom" =

        Table.AddColumn(

            Source,

            "Custom",

            each

                List.Transform(

                    Excel.Workbook([Content])[Data],

                    each Table.LastN(Table.FirstN(_, 8), 5)

                )

        )

V-lianl-msft_5-1619073903042.png


Comment:

1. The first each means the iterator of table level, so it work on table rows(Record type)

2. The second each is for list function, so it iterators on list content.(for this scenario, they are table, so I can invoke with Table.FirstN/LastN function)

3. _ means the current content that iterator looped. For table level, 'each' keyword is looping on the 'record' type of content. List levels are looping on the list items.

Summary:

If you want to do a nested operation, you need to know the function parameters, confirm the parameter types, and choose the correspond function type to handle them.  

In addition, 'each' iterator is a hard part to understand.  

For some functions that nested a few functions with different types, you cannot directly use the '_' symbol to replace the current content. 

For this scenario, you may need to use the 'function' structure to package operations and variable parameters with the current parameter in it to separate the conflict in content usage.

Reference links:

Expressions, values, and let expression - PowerQuery M | Microsoft Docs

Operators - PowerQuery M | Microsoft Docs

 

 

 


Author: Xiaoxin Sheng

Reviewer: Kerry & Ula