learn2excel/M

Collection of cutom M functions for Power Query and Power BI

M

This repo contains custom functions in the language M, which can be used in Power BI, Power Query (or Get & Transform) in Excel or in Analysis Services (version 2017 upwards).

This video describes how to use them: https://www.youtube.com/watch?v=6TQN6KPG74Q

There are currently 2 libraries:

• Library: Main library who holds pure M-functions
• LibraryR: Holds M-functions that contain R-code. So you need to have R installed for them to run. But once installed and configured, you can use these functions just like the other M-functions. Further details check out here: Using R in the Query Editor

Table of contents:

AccessingData

Function	Description
AccessingData.DropboxFolder.pq	Returns a table with contents from your selected Dropbox folder. The optional field 'folder' allows you to access sub-folders within the main folder.
PowerBI.DataModel.pq	Returns port number and database name of the latest Power BI data model opened with PBI Desktop.
AccessingData.MCodeFromFile.pq	Extract M-code from Excel- or Power BI files, see: http://www.thebiccountant.com/2017/10/15/bulk-extracting-power-query-m-code-from-multiple-pbix-files-in-power-bi/ .
GetCodeFromGitHub.pq	Fetches function code from GitHub
fnSQLDB_FirstNOfAllTables	fetches the first N rows in all tables (and views) from a database at once and presents them in a compact form
Syntax.UseSQL.pq	Use SQL-statement to query your M-tables (!!!) in your current file.

Allocation

Function	Description
Allocation.StretchAndCompress.pq	Allocates values by stretching or compressing a series of values over time

DAX

Function	Description
DAX.CalculateDebugger.pq	Produces DAX code to debug a filter argument of CALCULATE.

Date

Function	Description
Date.DatesBetween.pq	Creates a list of dates according to the chosen interval between Start and End. The dates created will always be at the end of the interval, so could be in the future if today is chosen.
Date.DimDate.pq	Standard DimDate gregorian calendar accd. To Marco Russo DAX calendar

DateTime

Function	Description
DateTime.HoursBetween.pq	Returns a list of hours (datetime) that lie between the Start and End

Documentation

Function	Description
Documentation.FunctionFieldValues.pq	Shows the values of the documentation-fields and the signature of a function

Function

Function	Description
Function.Pipe.pq	Allows to use piping-syntax: http://www.thebiccountant.com/2017/08/16/should-we-pipe-m/
Function.QuickBooksTimeActivity.pq	Fetches all available time activity fields from the QuickBooks connector. Optional parameter allows filtering after a certain date.

Http

Function	Description
Http.APIGenerateTokenPW.pq	Generates a Token for an API with username and password. Urls default to Power BI API

List

Function	Description
List.ChunkUp.pq	Chunks up an inputList into groups of chunkSize.
List.Percentile.pq	Returns the k-th percentile of values in a list. Use "1" in 3rd parameter to set from Inc (default) to Exc .
List.PermutationTable.pq	Creates a permuations table from all elements from a List
List.ToMCode.pq	Transforms a List to a string of M code that will create that list in the query editor.
List.SelectPositions.pq	Selects all items from SelectionList that are on positions in ListOfPositions.

NAV

Function	Description
NAV.AccountListFromTotaling.pq	Creates a list of account numbers from the totalling syntax in NAV chart of accounts or account schemes.

Number

Function	Description
Number.ModXls.pq	simple Number.Mod that returns the same like Excel or DAX when negative figures are involved

Other

Function	Description
Xlsx.ExtractQueries.pq	Extracts all queries from files in folder or xlsx-files
SQLFirstNRowsFromAllTables.pq	fetches the first N rows in all tables (and views) from a database at once and presents them in a compact form
Export.CreateGist.pq	Creates a secret gist with the parameters provided. Optional parameter to make it public.
ImportPdfTextR.pq	R script to import text from a pdf file. Returns a table with one row per page by default. Using 1 in the second optional parameter will return one row per line of text instead with matching indices.
M.Switch.pq	Evaluates an Expression against a list of Values and returns one or multiple possible Results expressions.
GetCodeFromGithub.pq

R Statistical Functions

Function	Description
R Trend.pq	R-function for trend (fitted(lm) and forecast (predict(lm)

Record

Function	Description
Record.FunctionToTable.pq	Transforms the function-record to a searcheable table showing metadata
Record.AllQueries.pq	Creates a record with different sorts of references to the existing queries in the query pane.

Statistic

Function	Description
XlsStat.Trend.pq	Calculates a trend according to Excel's TREND-function but without the option to define your own slope and intercept.

Table

Function	Description
Table.SolveParentChild.pq	Creates columns for all parents, multiple parents are supported
Table.ReferenceDifferentRow.pq	Adds columns to a Table with values from previous or next rows (according to the Step-index in the 2nd parameter)
Table.UnpivotKeepNulls.pq	Unpivots columns (or optional other columns) while keeping empty fields (with null). Any entry to the 3rd parameter will set to "Unpivot Other Columns" instead.
Table.ToFunctionRecord.pq	Converts a table with function code to a record where the function is active
Table.UnpivotByNumbers.pq	Unpivots a table according to the number of columns and header rows passed on in the parameters
ParentChildAllParents.pq	Creates columns for all parents, multiple parents are supported
Table.ToMarkdown.pq
Table.ContainsAnywhere.pq	Checks if a string or list of strings is contained somewhere in the table.
Table.ToMCode.pq	Transforms a Table to a string of M code that will create that table in the query editor.
Table.PreviousRow.pq	Superfast way to reference previous row
Table.SortB.pq	Buffered Table.Sort. Buffers the result to maintain the sort order in future referencing steps
Table.ExpandAllColumns.pq	Expands all columns from different tables within one column at once
Table.ReplaceMultiple.pq	Replaces multiple values at a time in a table column without recursion: http://www.thebiccountant.com/2016/05/22/multiple-replacements-in-power-bi-and-power-query/ The ReplacementsTable must have the values to be replaced in the 1st column and the new value in the 2nd.
Table.SolveParentChild.pq	Creates columns for all parents, multiple parents are supported
Table.ClusteredIndex.pq	Returns a Table with a new column with a specific name that, for each row, contains an index of the row in the table.
Date.DatesBetween.pq	Creates a list of dates according to the chosen interval between Start and End. Allowed values for 3rd parameter: "Year", "Quarter", "Month", "Week" or "Day".
Table.NestedJoinSQL.pq
TableFlattenJSON.pq	Flattens expanded JSON table
Table.DistinctCI.pq	Case insensitive Table.Distinct
Table.AddRollingSum	Adds a column with a rolling sum to a table.
Table.ColumnRunningTotal.pq	Fast way to add running total to a table
Table.ExportCsvPyhton.pq	Exports table to csv using Python-script.
Table.BlendDataTableau.pq	Blends table data like in Tableau. See: http://www.thebiccountant.com/2017/02/23/blending-data-in-powerbi-like-in-tableau/
TransformColumnTypesDynamically.pq	Dynamically transforms column types from table t according to the columns contens of its first 10 rows by default. The number of rows to be taken into consideration can be adjusted by the optional parameter maxSample.
Table.BillOfMaterialsBasic.pq	Dynamically solve a Bill of materials-structure, generating as many hierarch-columns as necessary. See: http://www.thebiccountant.com/2017/05/08/dynamic-bill-of-material-bom-solution-in-excel-and-powerbi/#more-1782 .
Table.ImputerColumn.pq	Replaces null values in a column with an aggregation of its existing values. Options: Replace nulls in all columns (leave the 3rd param. blank), replace with a figure from a different column (columns names goes into 4th param.), take row-aggregation instead/horizontal (use 1 in 5th param.).
Table.AddMergeOtherColumns.pq	Adds a column named MergedColumnName to a Table that merges all other columns than named OtherColumnNames. Delimiter as a text string.
Table.InnerJoinForSQLFolding.pq	Performs an inner join on a sql table with a non-sql-source that will fold. To be used as filter only, as none of the non-SQL columns can be expanded.
Table.PivotSingleColumn.pq	Transforms a Table with a single column into a table with NumberOfColumns.
Table.GroupDynamicAggregation.pq	Aggregates all columns from the SourceTable that are not included in GroupColumnNames with Sum as default operation. This can be changed in optional parameter AggregationFunction.
Table.ExportToCsv.pq	Exports a table to a csv file using R-script in PowerBI Desktop. You can pass the path in with backward-slashes, that will be reversed automatically.
Table.ColumnRunningTotal.pq	Fast way to add running total to a table
Table.JsonExpandAll.pq	Dynamically expands the Record and returns values in one column and additional columns to navigate.
Table.RenameColumnsWithFunction.pq	Returns a Table with the column names transformed according to MyFunction from the 2nd parameter
Table.ExpandAllXML.pq	Dynamically expands the table ("Name" and "Value" as column headers) and returns values in one column and additional columns to navigate.
Table.AddColumnT.pq	Adds a column to a Table_ that originates from a list holding the values for the newColumn . New columnName to be passed as the last argument.

Text

Function	Description
Text.BetweenDelimitersOccAll.pq	Returns all occurrances of text strings between a delimiter pair. Optional parameter Alternative_Dummy can be used if Dummy shall not be +.
Text.ShowHtmlTags.pq	Selects all Html tags from a text
Text.RemoveHtmlTags.pq	Removes all Html tags from a text
Text.BetweenDelimitersOccAll.pq	Returns all occurrances of text strings between a delimiter pair.
Text.RemoveRepeatingCharacters.pq	Removes repeating characters of the delimiter from a string
Text.QueryNames.pq	Creates a string with all query names from the current file. Can only be used in the query editor, as shared will not work when loaded to the data model
Text.FunctionRecordExpressionEvaluate.pq	Creates a string for a record or function (if 2nd parameter is used) for the function record in Expression.Evaluate, replacing #shared.
Text.RemoveBetweenDelimiters.pq	Removes text between 2 delimiters.

Type

Function	Description
Type.Text.pq	Returns the type of the Value in textform.
Type.AsText	Returns type in text format
Type.FromText	Returns a type from its textual representation.