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
| 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. |
| Function | Description |
|---|---|
| Allocation.StretchAndCompress.pq | Allocates values by stretching or compressing a series of values over time |
| Function | Description |
|---|---|
| DAX.CalculateDebugger.pq | Produces DAX code to debug a filter argument of CALCULATE. |
| 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 |
| Function | Description |
|---|---|
| DateTime.HoursBetween.pq | Returns a list of hours (datetime) that lie between the Start and End |
| Function | Description |
|---|---|
| Documentation.FunctionFieldValues.pq | Shows the values of the documentation-fields and the signature of a 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. |
| Function | Description |
|---|---|
| Http.APIGenerateTokenPW.pq | Generates a Token for an API with username and password. Urls default to Power BI API |
| 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. |
| Function | Description |
|---|---|
| NAV.AccountListFromTotaling.pq | Creates a list of account numbers from the totalling syntax in NAV chart of accounts or account schemes. |
| Function | Description |
|---|---|
| Number.ModXls.pq | simple Number.Mod that returns the same like Excel or DAX when negative figures are involved |
| 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 |
| Function | Description |
|---|---|
| R Trend.pq | R-function for trend (fitted(lm) and forecast (predict(lm) |
| 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. |
| 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. |
| 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. |
| 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. |
| 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. |