r/PowerQuery

*maybeThis function, a combination of Imke Feldmann's fnCreateDateIntervalTable, and a function I wrote, produces a date table for all dates in a given period, along with 81 different date parts / formats, to include Fiscal Year (FY Dates configurable in M code) . Invoking this function and creating a date table can prevent the need to add columns for each date part required in a query.

For anyone new to M, copy this code, then paste it in the advanced editor in Power Query, then rename the query from Query1 to fnSuperDateTableCreator (or whatever you want). From there, invoke the function by entering the start/end dates and any optional selections, save the resultant query as your date

Hi. I don’t have a problem specific to solve. Instead I am looking for insights as I merrily stumble along trying to learn PQ with excel. My average workbook has five to seven tabs with about 100 columns on 50K rows. About half the tabs are raw data imports and the others tend to mix elements in and are formula heavy. In short there are performance issues starting to come up. I read an article about how PQ can handle (IIRC) a million rows of data without a problem. How do you leverage that? I’ve started using it to automate the imports. Since it creates tables I get to learn something since I tend more to ranges. But, back to the rookie question… in general how can PQ help me with large data

I've been getting in to Power Query the last month - learning the basics and creating some tools that will help transform my data. I come from a programming background (although I've been out of that field for 20+ years), and I know there have to be tools for a better interaction with the language.

I downloaded VS Code and installed the Power Query extension. That's a much nicer way to interact with the code than the Advanced Editor. I'm just looking for some help with best practices for being more efficient/effective with Power Query:

1. Do you cut and paste the code from VS Code to the Advanced Editor?
2. Is there a tool to connect VS Code and Power Query workbook?
3. Is there some way t

Hello everyone. I’m an accountant working in industry where we go through the usual month end close. I feel like one of the more proficient users of excel in the general ledger department and I’ve been exposed to power query through some data analytics classes. However I’m having a hard time trying to find ways I can use it for some month end efficiencies and processes. Before I invest into money into classes (looking at Leila Gharani), can some of you provide me examples of what you’re using it for when it comes to month end close? I’d love to improve efficiency and automation around some reconciliations that are currently very manually created in excel making them prone to errors. I’ve cre

Learn to be more efficient with these Hidden power query keyboard shortcuts.

which of these are faster? I used to just do select columns from table, then filter the values i want after which for the data i need takes 10-15 minutes.. i tried select columns from table where column like etc and it doesnt seem faster but i might be wrong for larger datasets so which is best to practise to use?

There there keywords or operators don't make sense? For example:

• try something catch (e) => e[Message]
• a ?? b
• each [X] + 3

Hi I have a CSV file which gets automatically generated every morning. The date is represented in the filename: "whsops_yyyymmdd". Is there a way I can setup a query which filters and imports the given CSV on any date? I need to do this task daily.

I have alread written a VBA code but would love to just press "refresh" and get new data which can than be analysed by excel formulas.

Thanks for any help! Tim

Hello,

Any recommendations for books or long chapters that cover working with PDF's in Power Query?

Thanks.

Hello!

Hoping someone can help me work this out. I am trying to split this column which has a date and time, but the split column using the delimiter function doesn't split at the correct place.

Here is what I am trying to do:

https://preview.redd.it/1lryvb0wrjxa1.png?width=181&format=png&auto=webp&v=enabled&s=21d73ff9892d5d3fcb970a9bad9e83286acfa1b4

I am trying to separate the date from the time. When I choose split column by delimiter, it splits the column but after the 10:00 AM. See below:

​

https://preview.redd.it/5koz3ue6sjxa1.png?width=700&format=png&auto=webp&v=enabled&s=e810d18f569fe86583dda452b36c1b6126674054

The result:

https://pre

Hello All - was looking for an automated way to dump all the PowerQuery code in my workbook to a text file. Wrote this VBA code to dump it to a text file with the ".m" suffix. I use an XML plug-in to open this in Notepad++, which does syntax highlighting for ".m" files - it's found here. This dumps all the code from the queries in a single Excel workbook to one output file, concatenating them one after another.

Sub DumpPowerQueryCode()
	  dim iX 			as integer
	  dim iY			as integer
	  dim iW			as integer
          Dim sLines()                  As String
          Dim wbMain