Skip to content
Collection of cutom M functions for Power Query and Power BI
Branch: master
Clone or download
Latest commit 9f08b5e Jan 11, 2019
Permalink
Type Name Latest commit message Commit time
Failed to load latest commit information.
Library Delete test4.pq Jan 11, 2019
LibraryR Tranform to text before exporting Oct 7, 2018
LICENSE Initial commit Jul 25, 2017
README.md Update README.md Jul 11, 2018

README.md

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.
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/ ."

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"

Function

"Function Description
Function.MyFunctionsInTable.pq Returns a table with all custom functions in the library (ending with .pq), pass # s hared as environment
Function.Pipe.pq Allows to use piping-syntax: http://www.thebiccountant.com/2017/08/16/should-we-pipe-m/"

Other

"Function Description
GetCodeFromGithub.pq
SQLFirstNRowsFromAllTables.pq fetches the first N rows in all tables (and views) from a database at once and presents them in a compact form
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."

List

"Function Description
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.ToMCode.pq Transforms a List to a string of M code that will create that list in the query editor."

Table

"Function Description
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.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.BlendDataTableau.pq Blends table data like in Tableau. See: http://www.thebiccountant.com/2017/02/23/blending-data-in-powerbi-like-in-tableau/
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.
Table.ColumnRunningTotal.pq Fast way to add running total to a table
Table.PivotSingleColumn.pq Transforms a Table with a single column into a table with NumberOfColumns.
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.ToMCode.pq Transforms a Table to a string of M code that will create that table in the query editor.
Table.ToMarkdown.pq
Table.UnpivotByNumbers.pq Unpivots a table according to the number of columns and header rows passed on in the parameters
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.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."

Text

"Function Description
Text.RemoveHtmlTags.pq Removes all Html tags from a text
Text.RemoveRepeatingCharacters.pq Removes repeating characters of the delimiter from a string"
You can’t perform that action at this time.