Quick Steps – Reference

 

< Back to Help menu

Use this reference documentation to discover the syntax and arguments for the custom functions inside Power Query Quick Steps.

Contents

Click the links below to jump to the reference documentation information about each error.

Parameter Functions

Column Name Functions

Data Transformation Functions

Text Functions

Calculation Functions

Lookup Functions

Data Type Functions

Ranking & Order Functions

Date and Time Functions

Files & Folder Functions

Nested Table Functions

fxAutoCleanColumnNames

PURPOSE:
Changes columns names to new names based on rules. Inserts space and applies capitalization when a string:

  • Changes from numbers to text.
  • Changes from text to numbers.
  • Changes from lower case to upper case.
  • Includes an underscore.

SYNTAX:
fxAutoCleanColumnNames ( Table, [ColumnNamesList], [ListTypeIsInclude] )

  • Table (table) – Table or step to perform the transformation on.
  • [ColumnNamesList] (list of text) – List of column names to clean (e.g. {“Column1″,”Column2”}).
    • If excluded, apply to all columns.
  • [ListTypeIsInclude] (logical) – Switch to determine if ColumnNamesList includes or excludes the list of names to clean.
    • true: apply to items in the list
    • false: apply to items not in the list

NOTES:

  • Only use ColumnNamesList and ListTypeIsIncude where the action is to include or exclude specific column headers.
  • If used, both optional arguments are required.

fxAutoDetectDataType

PURPOSE:
Auto applies data types based on the data in a column.

SYNTAX:
fxAutoDetectDataType ( Table, [Threshold], [SampleSize] )

  • Table (table) – Table or step to perform the transformation on.
  • [Threshold] (number) – Declare the % of valid items before applying the data type (e.g. 0.75 – 75% of the data needs to be of a specific data type otherwise treated as ‘any’).
    • If omitted, default value is 1 (e.g., 100%).
  • [SampleSize] (number) – Declare how many records to include in the sample.
    • If omitted, default value is 10.

NOTES:

  • On large data sets, this transformation can be slow, so recommended to always run on a sample.
  • Where SampleSize exceeds the number of rows, the full data set is used.
  • Using a Threshold < 0 or > 1 results in all columns being ‘any’ data type.
  • DateTime data types are converted to Date where the time value is 00:00:00.
  • Detects Whole Numbers, Decimal Numbers, Dates, DateTimes, Logical and Text. Others are treated as ‘any’ or as ‘text.’
  • Based on a solution by Gil Raviv https://datachant.com/2018/05/14/automatic-detection-of-column-types-in-powerquery/

fxBlankToNull

PURPOSE:
Converts all blank values in a table or column to null.

SYNTAX:
fxBlankToNull ( Table, [ColumnNamesList] )

  • Table (table) – Table or step to perform the transformation on.
  • [ColumnNamesList] (list of text) – List of column names to convert blanks to null (e.g. {“Column1″,”Column2”}).
    • If excluded apply to all columns.

NOTES:

  • (None)

fxCalendarTable

PURPOSE:
Creates a calendar table based on start date & end date, or start date & duration.

SYNTAX:
fxCalendarTable ( StartDate, [EndDate], [DaysDuration] )

  • StartDate (date) – The first date in the calendar.
  • [EndDate] (date) – The last date in the calendar.
  • [DaysDuration] (date) – The number of days from the start date.

NOTES:

  • If EndDate is provided, a calendar is created using StartDate & EndDate.
  • If EndDate is null, a calendar is created using StartDate & DaysDuration.

fxCalendarTableNonStandard

PURPOSE:
Creates a calendar table from a table of non-calendar periods.

SYNTAX:
fxCalendarTableNonStandard ( Table, PeriodEndDateColumnName, NewDateColumnName )

  • Table (table) – Table containing data about the period ends.
  • PeriodEndDateColumnName (text) – Name of column containing the period end date.
  • NewDateColumnName (text) – Name to give to the new date column.

NOTES:

  • (None)

fxCartesianJoin

PURPOSE:
Performs a cartesian join on all tables included in a list. (i.e. creates a table of all combinations).

SYNTAX:
fxCartesianJoin ( TableList )

  • TableList (list of tables) – List of tables to join (e.g. {Table1,Table2,Table3}).

NOTES:

  • TableList may also include names of previous steps.

fxColumnReAlign

PURPOSE:
Re-align columns where data does not stack correctly in a single column.

SYNTAX:
fxColumnReAlign ( Table )

  • Table (table) – Table or step to perform the transformation on.

NOTES:

  • Re-alignment works where empty values are null values, and there are no genuine null values in the data set.

fxCombineWorksheetsByName

PURPOSE:
Combine worksheets from workbooks in a folder using sheet or table names.

SYNTAX:
fxCombineWorksheetsByName ( Table, ContentColumnName, ObjectName, PartialMatch, SheetOrTable,
[PromoteHeaders], [AutoExpand] )

  • Table (table) – Table or step containing the files in the folder.
  • ContentColumnName (text) – Name of column containing the workbook binaries.
  • ObjectName (text) – The table or sheet name to combine.
  • PartialMatch (logical) – Determines if only partial match required (includes ignoring case).
    • true: Partial match permitted.
    • false / null: Exact match only.
  • SheetOrTable (text) – Determine if objects to combine are sheets or tables.
  • [PromoteHeaders] (logical) – Should headers be promoted.
    • true: Promote headers.
    • false / null: Do not promote headers.
  • [AutoExpand] (logical] – Should data should be expanded automatically (dynamically).
    • true: Expand data.
    • false / null: Do not expand data.

NOTES:

  • (None)

fxCombineWorksheetsByPosition

PURPOSE:
Combine worksheets from workbooks in a folder using sheet or table names.

SYNTAX:
fxCombineWorksheetsByPosition ( Table, ContentColumnName, Position, SheetOrTable, [PromoteHeaders], [AutoExpand] )

  • Table (table) – Table or step containing the files in the folder.
  • ContentColumnName (text) – Name of column containing the workbook binaries.
  • Position (number) – Number of the sheet or table to combine (zero-based).
  • SheetOrTable (text) – Determine if objects to combine are sheets or tables.
  • [PromoteHeaders] – (logical) – Should headers be promoted.
    • true: Promote headers.
    • false / null: Do not promote headers.
  • [AutoExpand] (logical) – Should data should be expanded automatically (dynamically).
    • true: Expand data.
    • false / null: Do not expand data.

NOTES:

  • (None)

fxDeleteNullMoveLeft

PURPOSE:
Deletes null values in a column and shifts the cells left to fill the gap.

SYNTAX:
fxDeleteNullMoveLeft ( Table, ColumnName )

  • Table (table) – Table or step to perform the transformation on.
  • ColumnName (text) – Column name containing the null values to delete.

NOTES:

  • Works where empty values are represented by null and, and there are no genuine null values in the column.

fxExcelDataFromList

PURPOSE:
Uses a table with file path and data columns to get data from a list of Excel Workbooks.

SYNTAX:
fxExcelDataFromList (Table, FilePathColumnName, DataColumnName, [PromoteHeaders], [AutoExpand] )

  • Table (table) – Table or step to perform the transformation on.
  • FilePathColumnName (text) – The column name which contain the file paths.
  • DataColumnName (text) – The name of the data object in the workbook.
  • PromoteHeaders (logical) – Should headers be promoted prior to expanding.
    • true: Promote headers.
    • false / null: Do not promote headers.
  • AutoExpand (logical) – Should data should be expanded automatically (dynamically).
    • true: Expand data.
    • false / null: Do not expand data.

NOTES:

  • (None)

fxExpandColumnDynamic

PURPOSE:
Expands a column dynamically including adding any new data.

SYNTAX:
fxExpandColumnDynamic (Table, ColumnName, [PromoteHeaders] )

  • Table (table) – Table or step to perform the transformation on.
  • ColumnName (text) – Name of column containing the tables to expand.
  • [PromoteHeaders] (logical) – Should headers be promoted prior to expanding.
    • true: Promote headers.
    • false / null: Do not promote headers.

NOTES:

  • Use fxAutoDetectDataType to automatically apply data type to columns

fxFilesInSharePointFolder

PURPOSE:
Gets all the files in a named SharePoint folder based on a folder URL.

SYNTAX:
fxFilesInSharePointFolder (FullURL, [IncludeSubFolders])

  • FullURL (text) – The full URL of the SharePoint folder.
  • IncludeSubFolders (logical) – Should the files in subfolders be included in the files returned.
    • true: Include subfolders.
    • false / null: Exclude subfolders.

NOTES:

  • (None)

fxFillRightOrLeft

PURPOSE:
Fills null values with the values from the left or right.

SYNTAX:
fxFillRightOrLeft (Table, [FillRight] )

  • Table (table) – Table or step to perform the transformation on.
  • [FillRight] (logical) – Determine the direction to fill from.
    • true/null: fill values to the right.
    • false: fill values to the left.

NOTES:

fxFilterByList

PURPOSE:
Filters a table column based on a list.

SYNTAX:
fxFilterByList (Table, ColumnName, FilterList, [ListIsInclude] )

  • Table (table) – Table or Step name to perform the transformation on.
  • ColumnName (text) – Name of column containing values.
  • FilterList (list) – List to filter by (e.g. {“Alpha”,”Bravo”,”Charlie”} or {1,2,3}).
  • [ListIsInclude] (logical) – Does the list exclude or include the items in the list.
    • true / null: Include the items in the list.
    • false: Exclude the items in the list.

NOTES:

  • (None)

fxFinancialPeriod

PURPOSE:
Adds a Financial Month, Year, or Quarter column.

SYNTAX:
fxFinancialPeriod ( Table, DateColumnName, FinancialYearEndMonth, NewColumnName, TimePeriod )

  • Table (table) – Table or step to perform the transformation on.
  • DateColumnName (text) – Name of the column containing dates.
  • FinancialYearEndMonth (number) – Month number of the financial year-end.
  • NewColumnName (text) – Name of the new column.
  • TimePeriod (text) – Set return value as Financial Year or Financial Month.
    • “Month”: Returns the Financial Month.
    • “Quarter”: Return the Financial Quarter.
    • “Year”: Return the Financial Year.

NOTES:

  • Assumes financial periods end on the last day of each month.

fxFlattenHeaderRows

PURPOSE:
Flattens multiple header rows into a single header row.

SYNTAX:
fxFlattenHeaderRows ( Table, HeaderRowCount, Separator, [FillDirection] )

  • Table (table) – Table or step to perform the transformation on.
  • HeaderRowCount (number) – Number of header rows to flatten.
  • Separator (text) – The character(s) to use as a separator between the individual elements of the final header row.
  • [FillDirection] (text) – The direction to fill the missing column data.
    • “Right”: Fill to the right.
    • “Left”: Fill to the left.
    • Null: no fill.

NOTES:

  • The header rows must be in the first rows of the data; not part of an existing header row.

fxGetNameParameter

PURPOSE:
Returns a value from a named range in the current workbook to use as a parameter inside a query.

SYNTAX:
fxGetNameParameter ( NamedRange, [DataTypeName] )

  • NamedRange (text) – Excel named range containing the parameter.
  • [DataTypeName] (text) – Data type of the parameter value.
    • Valid values include “Text”, “Number”, “Date/Time”, “Date”, “Time”, “Duration”, “True/False“.
    • If excluded, the default type is Any.

NOTES:

  • Based on fnGetParameter function from Master Your Data With Power Query (Puls / Escobar)

fxGetTableParameter

PURPOSE:
Returns a value from a table in the current workbook to use as a parameter inside a query.

SYNTAX:
fxGetTableParameter ( TableName, ParameterName, ParameterNameColumn, ParameterValueColumn, [DataTypeName] )

  • TableName (text) – Table containing the parameter.
  • ParameterName (text) – Name of the parameter in the table.
  • ParameterNameColumn (text) – Column name containing the parameter names.
  • ParameterValueColumn (text) – Column name containing the value to return.
  • [DataTypeName] (text) – Data type of the parameter value.
    • Valid values include “Text”, “Number”, “Date/Time”, “Date”, “Time”, “Duration”, “True/False“.
    • If excluded, the default type is Any.

NOTES:

  • Based on fnGetParameter function from Master Your Data With Power Query (Puls / Escobar)

fxGroupColumnUnpivot

PURPOSE:
Unpivots multiple columns presented in a repeating column interval.

SYNTAX:
fxGroupColumnUnpivot ( Table, GroupLength, [KeepColumnNamesList], [NewColumnNamesList] )

  • Table (table) – Table or Step name to perform the transformation on.
  • Group Length (number) – Number of columns contained in each group of columns,
  • [KeepColumnNamesList] (list) – List of column names that are unchanged.
  • [NewColumnNamesList] (list) – List of names to apply to unpivoted columns. Default column names applied if null.

NOTES:

  • NewColumnNamesList must contain the same number of items as the repeating column pattern.

fxGroupRowUnpivot

PURPOSE:
Unpivots multiple rows presented in a repeating row interval.

SYNTAX:
fxGroupRowUnpivot ( Table, KeepColumnNamesList, [NewColumnNamesList], [MissingDataFillDirection] )

  • Table (table) – Table or step to perform the transformation on.
  • KeepColumnNamesList (list) – List of column names that are unchanged.
  • NewColumnNamesList (list) – List of names to apply to unpivoted columns. Default names applied if null.
  • MissingDataFillDirection (text) – Direction to fill the KeepColumnsNamesList to avoid null values.
    • “Up” – Fills up
    • “Down” / null / any other value – Fills down

NOTES:

  • If used, NewColumnNamesList must contain the same number of items as the repeating row pattern.

fxGroupRunningTotal

PURPOSE:
Adds a running total column where the total resets at the start of each group.

SYNTAX:
fxGroupRunningTotal ( Table, ColumnName, NewColumnName , GroupByColumnNamesList , [RetainOrder] )

  • Table (table) – Table or step to perform the transformation on.
  • ColumnName (text) – Column name to perform the calculation on.
  • NewColumnName (text) – Name of the new running total column.
  • GroupByColumnNamesList (list of text) – List of the column names to group by.
  • [RetainOrder] (logical) – Should data be presented in the original order.
    • true: values are returned to their original order.
    • false / null: values ordered within each group.

NOTES:

fxLookupApproximateMatch

PURPOSE:
Returns an approximate match lookup from another table.

SYNTAX:
fxLookupApproximateMatch ( Table, ValueColumnName, LookupTable , LookupValueColumnName, LookupReturnColumnName, NewColumnName, [LessThanOrMoreThan] )

  • Table (table) – Table or step to perform the transformation on.
  • ValueColumnName (text) – Name of the column containing the lookup value.
  • LookupTable (table) – Table to lookup the value from.
  • LookupValueColumnName (text) – Name of the column to lookup.
  • LookupReturnColumnName (text) – Name of the column to return value from.
  • NewColumnName (text) – The name of the column to be added.
  • [LessThanOrMoreThan] (text) – Should the return value be less than or equal to, or more than or equal to the lookup value.
    • “LessThan” / [any other value]: Return values less than or equal to the value.
    • “MoreThan”: Return values more than or equal to the value.

NOTES:

  • Based on approximate match joins as demonstrated in Master Your Data With Power Query by Puls/Escobar

fxLookupExactMatch

PURPOSE:
Returns an exact match lookup from another table.

SYNTAX:
fxLookupExactMatch ( Table, ValueColumnNameList, LookupTable , LookupValueColumnNameList, LookupReturnColumnName, NewColumnName, [ReturnOptions] )

  • Table (table) – Table or step to perform the transformation on.
  • ValueColumnNameList (list of text) – Name of the columns containing the lookup value as a list (e.g. {“Col1″,”Col2″,”Col3”} for multicolumn, or {“Col1”} for a single column).
  • LookupTable (table) – Table to lookup the value from.
  • LookupValueColumnNameList (list of text) – Name of columns to lookup (e.g. {“Col1″,”Col2″,”Col3”} for multicolumn, or {“Col1”} for a single column).
  • LookupReturnColumnName (text) – Name of the column to return value from.
  • NewColumnName (text) – Name of the column to be added.
  • [ReturnOptions] (text) – Which item(s) should the lookup return.
    • “First”: Return the first item.
    • “Last”: Return the last item.
    • [any other value] / null: Return all items.

NOTES:

  • ValueColumnNameList and LookupValueColumnNameList must contain the same number of items in the same order.

fxMultiFindReplace

PURPOSE:
Find and replace values based on a list.

SYNTAX:
fxMultiFindReplace ( Table, ColumnNamesList, FindList, ReplaceList, PartialMatch )

  • Table (table) – Table or step to perform the transformation on.
  • ColumnNamesList (list of text) – List of column names to perform the find and replace action on.
  • FindList (list of text) – List of text to find (e.g. {“A”,”B”,”C”}).
  • ReplaceList (list of text) – List of text to replace (e.g. {“D”,”E”,”F”}).
  • PartialMatch (logical) – Can the find and replace be a partial match.
    • true: Find and replace is performed on a partial text string.
    • false: Find and replace is performed on entire cell values only.

NOTES:

  • The find and replace values must be in corresponding locations in each list.
  • The transformation is case-sensitive.

fxPivotAllRows

PURPOSE:
Pivots all rows without causing an error.

SYNTAX:
fxPivotAllRows ( Table, PivotByColumnName, PivotValuesColumnName )

  • Table (table) – Table or step to perform the transformation on.
  • PivotByColumnName (text) – Name of column to pivot on.
  • PivotValuesColumnName (text) – Name of values column to include in the pivot.

NOTES:

fxPreviousRow

PURPOSE:
Returns or calculates a value based on a previous or subsequent row in the table.

SYNTAX:
fxPreviousRow (Table, ColumnName, NewColumnName, RowOffset, [ReturnOptions] )

  • Table (table) – Table or step to perform the transformation on.
  • ColumnName (text) – Name of column containing values.
  • NewColumnName (text) – The name for the new column.
  • RowOffset (number) – A number representing the rows to offset by.
  • [ReturnOptions] (text) – Determine the type of value returned.
    • “Value” / null: Returns the previous value.
    • “Variance”: Returns the numeric variance.
    • “Percentage”: Returns the percentage variance.

NOTES:

fxRemoveTopBottomNull

PURPOSE:
Removes null values from top and/or bottom of a table.

SYNTAX:
fxRemoveTopBottomNull ( Table, ColumnName, [ReturnOptions] )

  • Table (table) – Table or step to perform the transformation on.
  • ColumnName (text) – Name of column to base transformation on.
  • [ReturnOptions] (text) – Which null values should be removed.
    • “Top”: Remove null values from the top.
    • “Bottom”: Remove null values from the bottom.
    • [any other value] / null: Remove null values from Top and Bottom.

NOTES:

  • (None)

fxRemoveTopRowsUntilValue

PURPOSE:
Removes an unknown number of rows from the top until search term found.

SYNTAX:
fxRemoveTopRowsUntilValue ( Table, SearchColumnName, SearchValue, [PartialMatch], [InstanceNumber] )

  • Table (table) – Table or step to perform the transformation on.
  • SearchColumnName (text) – Name of column to search.
  • SearchValue (any) – Value to search for.
  • [PartialMatch] (logical) – Determines if only partial match required (includes ignoring case).
    • true: Partial match permitted.
    • false / null: Exact match only.
  • [InstanceNumber] (number): Where multiple matches exist, which instance to use as the basis for removing rows.

NOTES:

  • (None)

fxRemoveNullColumns

PURPOSE:
Removes any columns with 100% null values.

SYNTAX:
fxRemoveNullColumns ( Table )

  • Table (table) – Table or step to perform the transformation on.

NOTES:

  • (None)

fxRenameColumns

PURPOSE:
Renames columns, if the names exist.

SYNTAX:
fxRenameColumns ( Table, ColumnNamesList, NewColumnNamesList )

  • Table (table) – Table or step to perform the transformation on.
  • ColumnNamesList (list of text) – List of columns to rename (e.g. {“Column1″,”Column2″,”Column3”}).
  • NewColumnNamesList (list of text) – List of new column names (e.g. {“NewColumn1”, “NewColumn2”, “NewColumn3”}).

NOTES:

  • The old and new column names must be in the corresponding location in each list; therefore, the lists will contain the same number of items.

fxRenameColumnsByPosition

PURPOSE:
Renames columns based on their position (zero based).

SYNTAX:
fxRenameColumnsByPosition ( Table, ColumnPositionsList, NewColumnNamesList )

  • Table (table) – Table or step to perform the transformation on.
  • ColumnPositionsList (list of numbers) – List of column numbers (e.g. {0,2,4}).
  • NewColumnNamesList (list of text) – List of new column names (e.g. {“NewColumn1”, “NewColumn2”, “NewColumn3”}).

NOTES:

  • The column positions and new column names must be in the corresponding location in each list; therefore, the lists will contain the same number of items.

fxRepeatValueToNull

PURPOSE:
Changes repeat values in a column to null.

SYNTAX:
fxRepeatValueToNull ( Table, ColumnNamesList )

  • Table (table) – Table or step to perform the transformation on.
  • ColumnNamesList (list of text) – List of column names containing the repeat values to change to null.

NOTES:

  • (None)

fxRowNumberByGroup

PURPOSE:
Adds a row number for each item in a group.

SYNTAX:
fxRowNumberByGroup ( Table, GroupByColumnNamesList, NewColumnName, [RetainOrder] )

  • Table (table) – Table or step to perform the transformation on.
  • GroupByColumnNamesList (list of text) – List of the column names to group by.
  • NewColumnName (text) – Name of the column to add.
  • [RetainOrder] (logical) – Should data be presented in the original order.
    • true: values are returned to their original order.
    • false / null: values ordered within each group.

NOTES:

  • (None)

fxRunningTotal

PURPOSE:
Adds a running total column.

SYNTAX:
fxRunningTotal ( Table, ColumnName , NewColumnName )

  • Table (table) – Table or step to perform the transformation on.
  • ColumnName (text) – Column name to perform the calculation on.
  • NewColumnName (text) – Name of the new running total column.

NOTES:

fxTextRemoveSpaces

PURPOSE:
Removes excess spaces from text values.

SYNTAX:
fxTextRemoveSpaces ( Table, [ColumnNamesList] )

  • Table (table) – Table or step to perform the transformation on.
  • [ColumnNamesList] (list of text) – List of column names on which to remove spaces.

NOTES:

  • (None)

fxTimeGroups

PURPOSE:
Groups time into Hour, Minute and Second time segments.

SYNTAX:
fxTimeGroups ( Table, ColumnName, Units, UnitGroupSize, RoundType, NewColumnName )

  • Table (table) – Table or step to perform the transformation on.
  • ColumnName (text) – Name of the column containing the time or datetime.
  • Units (text) – Text value of time units to group by.
    • “Hours”: Hour time segments.
    • “Minutes”: Minute time segments.
    • “Seconds” / [any other value] / null: Second time segments.
  • UnitGroupSize (number) – The length of each unit group (e.g. When Units = “Minutes”, 5 is 5 Minute segments).
  • RoundType (text) – Text value of the how to round each value.
    • “Up”: Round up.
    • “Nearest”: Round to nearest.
    • “Down” / [any other value] / null: Round down.
  • NewColumnName (text) – The name of the column to be added.

NOTES:

  • (None)

fxTimeTable

PURPOSE:
Creates a time table based in hour, minute, or second intervals.

SYNTAX:
fxTimeTable ( [Units] )

  • Units – Text value of time units.
    • “Hours”: Hour time segments.
    • “Minutes”: Minute time segments.
    • “Seconds” / [any other value] / null: Second time segments.

NOTES:

  • (None)

fxTransformNestedTable

PURPOSE:
Performs table transformations on nested tables.

SYNTAX:
fxTransformNestedTable (Table, ColumnName, eachFunctionList )

  • Table (table) – Table or step containing the nested tables.
  • ColumnName (text) – Name of column containing the nested tables.
  • eachFunctionList (List of nested functions) – List of functions to perform on the nested tables.
    • Every function must be preceded by the word each.
    • Refer to nested tables with an underscore ( _ ).

NOTES:

  • (None)

fxUnstackOnInterval

PURPOSE:
Unstacks a column of data into separate columns based on a repeating numerical interval.

SYNTAX:
fxUnstackOnInterval ( Table, ColumnName, Interval )

  • Table (table) – Table or step to perform the transformation on.
  • ColumnName (text) – Name of column containing the stacked data.
  • Interval (number) – Length of repeating interval.

NOTES:

  • (None)

fxUnstackOnValue

PURPOSE:
Unstacks a column of data into separate columns based on the occurrence of a text string.

SYNTAX:
fxUnstackOnValue ( Table, ColumnName, SearchValue, [IgnoreCase] )

  • Table (table) – Table or step to perform the transformation on.
  • ColumnName (text) – Name of column containing the stacked data.
  • SearchValue (text) – Text value to split on.
  • [IgnoreCase] (logical) – Should case be ignored when matching the search value.
    • true: ignore case for the search.
    • false / null: apply case for search.

NOTES:

  • (None)