A collection of functions for use in Excel Power Query
Switch branches/tags
Nothing to show
Clone or download
Permalink
Failed to load latest commit information.
.gitignore why is .gitignore not working... Apr 12, 2016
ByD.GetReportData.pq Rename ByD.GetReportData.m to ByD.GetReportData.pq Sep 20, 2017
ByD.GetReportDataIDs.pq Rename ByD.GetReportDataIDs.m to ByD.GetReportDataIDs.pq Sep 20, 2017
ByD.GetReportMetadataIdName.pq Rename ByD.GetReportMetadataIdName.m to ByD.GetReportMetadataIdName.pq Sep 20, 2017
CrossUsage.pq Fix case mismatch in module name Dec 7, 2017
Date.EndOfLastMonth.pq Rename Date.EndOfLastMonth.m to Date.EndOfLastMonth.pq Sep 20, 2017
Date.EpochToDate.pq Rename Date.EpochToDate.m to Date.EpochToDate.pq Sep 20, 2017
Date.FormatSQL.pq Rename Date.FormatSQL.m to Date.FormatSQL.pq Sep 20, 2017
Date.NDaysAgo.pq Rename Date.NDaysAgo.m to Date.NDaysAgo.pq Sep 20, 2017
Date.NDaysInFuture.pq Rename Date.NDaysInFuture.m to Date.NDaysInFuture.pq Sep 20, 2017
Date.StartOfCurrentYear.pq Rename Date.StartOfCurrentYear.m to Date.StartOfCurrentYear.pq Sep 20, 2017
Date.StartOfLastYear.pq Rename Date.StartOfLastYear.m to Date.StartOfLastYear.pq Sep 20, 2017
Date.StartOfMonth12MAgoExcCurMonth.pq Rename Date.StartOfMonth12MAgoExcCurMonth.m to Date.StartOfMonth12MAg… Sep 20, 2017
Date.StartOfMonth12MAgoIncCurMonth.pq Rename Date.StartOfMonth12MAgoIncCurMonth.m to Date.StartOfMonth12MAg… Sep 20, 2017
Date.Today.pq Rename Date.Today.m to Date.Today.pq Sep 20, 2017
Date.Tomorrow.pq Rename Date.Tomorrow.m to Date.Tomorrow.pq Sep 20, 2017
Date.Yesterday.pq Rename Date.Yesterday.m to Date.Yesterday.pq Sep 20, 2017
DateTime.FormatSQL.pq Rename DateTime.FormatSQL.m to DateTime.FormatSQL.pq Sep 20, 2017
DateTime.UnixTime.pq Rename DateTime.UnixTime to DateTime.UnixTime.pq May 20, 2018
F.pq Rename F.m to F.pq Sep 20, 2017
Files.SheetsInFolder.pq Update Files.SheetsInFolder.pq Apr 12, 2018
Function.Profile.pq Rename Function.Profile.m to Function.Profile.pq Sep 20, 2017
LICENSE Remove ignored files Apr 12, 2016
List.CrossJoin.pq Rename List.CrossJoin.m to List.CrossJoin.pq Sep 20, 2017
List.Dates.HolidaysRU.pq Rename List.Dates.HolidaysRU.m to List.Dates.HolidaysRU.pq Sep 20, 2017
List.Dates.LastNDays.pq Rename List.Dates.LastNDays.m to List.Dates.LastNDays.pq Sep 20, 2017
List.Dates.LastNYears.pq Rename List.Dates.LastNYears.m to List.Dates.LastNYears.pq Sep 20, 2017
List.Dates.LastNYearsAndCurYear.pq Rename List.Dates.LastNYearsAndCurYear.m to List.Dates.LastNYearsAndC… Sep 20, 2017
List.Dates.NYearsToDate.pq Rename List.Dates.NYearsToDate.m to List.Dates.NYearsToDate.pq Sep 20, 2017
List.Dates.R12MExcCurMonth.pq Rename List.Dates.R12MExcCurMonth.m to List.Dates.R12MExcCurMonth.pq Sep 20, 2017
List.Dates.R12MIncCurMonthToDate.pq Rename List.Dates.R12MIncCurMonthToDate.m to List.Dates.R12MIncCurMon… Sep 20, 2017
List.Dates.YTD.pq Rename List.Dates.YTD.m to List.Dates.YTD.pq Sep 20, 2017
List.Dates.YTDLastYear.pq Rename List.Dates.YTDLastYear.m to List.Dates.YTDLastYear.pq Sep 20, 2017
List.DatesBetween.pq Rename List.DatesBetween.m to List.DatesBetween.pq Sep 20, 2017
List.FlatMap.pq Rename List.FlatMap.m to List.FlatMap.pq Sep 20, 2017
List.RankDense.pq Rename List.RankDense.m to List.RankDense.pq Sep 20, 2017
List.RankEqual.pq Rename List.RankEqual.m to List.RankEqual.pq Sep 20, 2017
List.RemoveErrors.pq Rename List.RemoveErrors.m to List.RemoveErrors.pq Sep 20, 2017
List.RemoveErrorsV2.pq Rename List.RemoveErrorsV2.m to List.RemoveErrorsV2.pq Sep 20, 2017
List.ReplaceErrors.pq Rename List.ReplaceErrors.m to List.ReplaceErrors.pq Sep 20, 2017
List.ToRecord.pq Rename List.ToRecord.m to List.ToRecord.pq Sep 20, 2017
List.ToText.pq Create List.ToText.pq May 20, 2018
List.Zip.pq Rename List.Zip.m to List.Zip.pq Sep 20, 2017
Load.pq Update Load.pq Sep 21, 2017
LoadFunctionFromGithub.pq Update LoadFunctionFromGithub.pq Sep 21, 2017
LoadPath.example.pq Update LoadPath.example.pq Sep 21, 2017
LoadTrace.pq Rename LoadTrace.m to LoadTrace.pq Sep 20, 2017
M.UDFs.pq Rename M.UDFs.m to M.UDFs.pq Sep 20, 2017
M.constants.pq Rename M.constants.m to M.constants.pq Sep 20, 2017
M.functions.pq Rename M.functions.m to M.functions.pq Sep 20, 2017
M.library.pq Rename M.library.m to M.library.pq Sep 20, 2017
M.types.pq Rename M.types.m to M.types.pq Sep 20, 2017
Misc.Wait.pq Rename Misc.Wait.m to Misc.Wait.pq Sep 20, 2017
Number.Dec2Bin.pq Renamed @Reccursion function name Apr 12, 2018
Old.Load.pq Update Old.Load.pq Sep 21, 2017
README.md related projects Apr 15, 2018
Record.Rename.pq Rename Record.Rename.m to Record.Rename.pq Sep 20, 2017
Record.Transform.pq Rename Record.Transform.m to Record.Transform.pq Sep 20, 2017
Record.TransformJoin.pq Rename Record.TransformJoin.m to Record.TransformJoin.pq Sep 20, 2017
Stat.Trend.pq Rename Stat.Trend.m to Stat.Trend.pq Sep 20, 2017
Table.AddScrape.pq Rename Table.AddScrape.m to Table.AddScrape.pq Sep 20, 2017
Table.CrossJoin.pq Rename Table.CrossJoin.m to Table.CrossJoin.pq Sep 20, 2017
Table.CrossJoinTables.pq Rename Table.CrossJoinTables.m to Table.CrossJoinTables.pq Sep 21, 2017
Table.ExpandAll.pq Rename Table.ExpandAll.m to Table.ExpandAll.pq Sep 21, 2017
Table.ExpandRecords.pq Rename Table.ExpandRecords.m to Table.ExpandRecords.pq Sep 21, 2017
Table.ExpandTables.pq Rename Table.ExpandTables.m to Table.ExpandTables.pq Sep 21, 2017
Table.ExpandTablesRecursive.pq Rename Table.ExpandTablesRecursive.m to Table.ExpandTablesRecursive.pq Sep 20, 2017
Table.FromString.pq Rename Table.FromString.m to Table.FromString.pq Sep 20, 2017
Table.JoinMany.pq Rename Table.JoinMany.m to Table.JoinMany.pq Sep 20, 2017
Table.MergeColumns.pq Rename Table.MergeColumns.m to Table.MergeColumns.pq Sep 20, 2017
Table.RemoveBlankColumns.pq Rename Table.RemoveBlankColumns.m to Table.RemoveBlankColumns.pq Sep 20, 2017
Table.RowsCombination.pq Rename Table.RowsCombination.m to Table.RowsCombination.pq Sep 21, 2017
Table.RowsCombination2.pq Rename Table.RowsCombination2.m to Table.RowsCombination2.pq Sep 20, 2017
Table.TranslateColumn.pq Rename Table.TranslateColumn.m to Table.TranslateColumn.pq Sep 20, 2017
Table.UnpivotByNumbers.pq Rename Table.UnpivotByNumbers.m to Table.UnpivotByNumbers.pq Sep 20, 2017
Text.AsciiOnly.pq Rename Text.AsciiOnly.m to Text.AsciiOnly.pq Sep 20, 2017
Text.Between.pq Rename Text.Between.m to Text.Between.pq Sep 20, 2017
Text.ContainsAny.pq Rename Text.ContainsAny.m to Text.ContainsAny.pq Sep 20, 2017
Text.Count.pq Rename Text.Count.m to Text.Count.pq Sep 20, 2017
Text.EachBetween.pq Rename Text.EachBetween.m to Text.EachBetween.pq Sep 20, 2017
Text.EachFromTo.pq Rename Text.EachFromTo.m to Text.EachFromTo.pq Sep 20, 2017
Text.FromTo.pq Rename Text.FromTo.m to Text.FromTo.pq Sep 20, 2017
Text.Like.pq Rename Text.Like.m to Text.Like.pq Sep 20, 2017
Text.MixedSort.pq Fixed query Apr 12, 2018
Text.PowerTrim.pq Rename Text.PowerTrim.m to Text.PowerTrim.pq Sep 20, 2017
Text.Queries.pq Rename Text.Queries.m to Text.Queries.pq Sep 20, 2017
Text.RemoveSymbols.pq Rename Text.RemoveSymbols.m to Text.RemoveSymbols.pq Sep 20, 2017
Text.ReplaceAll.pq Rename Text.ReplaceAll.m to Text.ReplaceAll.pq Sep 21, 2017
Time.EpochToTime.pq Rename Time.EpochToTime.m to Time.EpochToTime.pq Sep 20, 2017
Type.ToText.pq Rename Type.ToText.m to Type.ToText.pq Sep 20, 2017
UdfContents.pq Rename UdfContents.m to UdfContents.pq Sep 20, 2017
Value.ToText.pq Rename Value.ToText.m to Value.ToText.pq Sep 20, 2017
Value.TypeToText.pq Rename Value.TypeToText.m to Value.TypeToText.pq Sep 20, 2017
Value.WaitFor.pq Rename Value.WaitFor.m to Value.WaitFor.pq Sep 20, 2017
Vlookup.pq Rename vlookup.pq to Vlookup.pq Sep 20, 2017
Web.ContentsCustomRetry.pq Rename Web.ContentsCustomRetry.m to Web.ContentsCustomRetry.pq Sep 20, 2017
Web.Curl.pq Rename Web.Curl.m to Web.Curl.pq Sep 20, 2017
Web.FetchSequentially.pq Rename Web.FetchSequentially.m to Web.FetchSequentially.pq Sep 20, 2017
Web.Scrape.pq Rename Web.Scrape.m to Web.Scrape.pq Sep 20, 2017
Web.TimeAndDateCom.GetCountries.pq Rename Web.TimeAndDateCom.GetCountries.m to Web.TimeAndDateCom.GetCou… Sep 20, 2017
Web.TimeAndDateCom.GetCountryCalendar.pq Rename Web.TimeAndDateCom.GetCountryCalendar.m to Web.TimeAndDateCom.… Sep 20, 2017
getUdfContent.pq Update extension of files for m to pq Apr 12, 2018

README.md

pquery

This is a collection of functions written in the M language for use in Microsoft Excel's Power Query plugin.

Introduction

Rather than manually adding the functions to different Excel workbooks, users can instead leave their files in any directory, and either batch import them into your workbook using Excel 2016 VBA (see my sample workbook here), or by dynamically loading them into Power Query using something like the Load() function (see Load.pq). To use Load() you'll need to add it to every applicable workbook though. You can hard-code in the path to the folder where you put the functions from this repository, or set it in the specified cell in the above-mentioned sample workbook for use in that.

To manually add the Load query, click 'From Other Sources' in the Power Query ribbon tab, select 'Blank Query' (bottom option), open the Advanced Editor in the View tab, and paste in the below snippet (after adjusting file path). Click Done, name the query 'Load', and click 'Apply & Close' in the Home tab.

If you have your M queries in files, you may well prefer to edit them from a text editor like Notepad++. With Matt Mason's language file for this you will get nice color coding too!

The main point here is that by separating universally useful functions from an individual workbook, you will feel encouraged to use more modular code, solving each common sub-problem only once, rather than remaining stuck in 'vanilla' M and resolving the same problems repeatedly.

Moreover, coding this way will also further facilitate sharing code with other Power Query users, allowing for a more collaborative environment.

Admittedly, Microsoft languages have rarely been known for encouraging open-source collaboration, and the Power Query community is currently small. But will that mean we cannot assemble a powerful code library like those of the JavaScript community?

Usage

to use M code in workbooks without having to import every query/function:

  • get and unzip this repo, or in case you'd like to contribute back, open a command prompt in your desired location (in Windows Explorer type cmd in the address bar) and run command git clone https://github.com/tycho01/pquery.git.
  • copy LoadPath.example.pq as LoadPath.pq and replace its entire content with the path where you put the query files; e.g. "D:\pquery\".
  • manually import the Load.pq and LoadPath.pq functions into your workbook, keeping their names as Load and LoadPath.
  • now use the Load function to load queries from the folder you specified. i.e. if you write Text_Between = Load("Text.Between"),, it's going to give you the function located at YOUR_PATH\Text.Between.pq.

to allow sharing your workbook:

Related Projects (feel free to add!)

For more Power Query related repos check out some of the following users (sorted alphabetically, feel free to add!):