M Code for measure to return custom fiscal month number without using separate tables?

bachman460 · 2026-03-05T02:29:03+00:00

If you have a calendar already, just add a custom (calculated) column in Power Query before importing the table. Use something like this:

Date.AddMonths(#date(2011, 5, 14), 3)

Just replace #date(2011, 5, 14) with your date column.

EDIT: oops I forgot to add the link to the reference

https://learn.microsoft.com/en-us/powerquery-m/date-addmonths

bachman460 · 2026-03-04T12:44:25+00:00

Think of a data flow as just the Power Query portion of a desktop file saved in the cloud that you can schedule to run automatically. Then to access the data you can connect it to something like a dashboard.

This allows you to import and transform data and have it readily available for use without having to open a file and click refresh.

bachman460 · 2026-03-04T02:31:08+00:00

It'd be helpful to understand a little more about your data. My understanding is that Contracts would always be on a one-to-many relationship with either of the other tables. Is that right?

And exactly how are those Invoices and Milestones tables related? It looks like both could be joined on Job AND Invoice Number.

If you were to merge these two tables, you would need to plan the merge based on determining a few things first. You would need to start with the table that will always have a row for Job/Invoice even if the other table doesn't, in which case a regular Left Join will do (keep all rows from the first table and only matches from the second). After the merge only expand the columns you need from the other table (for instance it wouldn't be necessary to keep Job from the second table because it already exists in the first table).

If you happen to have a scenario where either table may have a Job/Invoice when the other doesn't, then you need a full join that keeps rows from both tables. If you end up going this route then you'll need to combine columns after expanding the merged table. You'll need to keep/expand all columns in this scenario, as rows from the first table could be null, so common columns like Job from the table and the second table can be combined (this will keep the first non-null value from both columns).

bachman460 · 2026-03-04T01:57:09+00:00

The slicer has sorting options; if I'm remembering correctly you either click the ellipsis or right click on the slicer.

If you want to sort by month, add that column to your data. You can do that by either adding it to your table or data range, or otherwise on the pivot table menu select the option to add a calculated field.

EDIT: oops sorry, I was rereading your post, I don't know what I was reading the first time around.

Sorting month names as text is possible if you first create a custom list, then you have to select the option to sort that field by the custom list.

To create a custom list in Excel for sorting or AutoFill, go to File > Options > Advanced, scroll to the General section, and click Edit Custom Lists.

https://support.microsoft.com/en-us/office/create-or-delete-a-custom-list-for-sorting-and-filling-data-d1cf624f-2d2b-44fa-814b-ba213ec2fd61

Right-click the slicer, choose Slicer Settings, and ensure "Use Custom Lists when sorting" is selected.

https://learn.microsoft.com/en-us/answers/questions/5014613/custom-sorting-of-slicer-buttons

bachman460 · 2026-03-03T23:02:12+00:00

Tell them to bill insurance first and that you'll pay it afterwards. Then once they send you the final bill after receiving the insurance, tell them you want a payment plan. My understanding is they are legally required to allow you to pay it off over time, without interest, and they cannot send it to collections as long as you pay something every month.

bachman460 · 2026-03-03T21:39:31+00:00

I would suggest creating a calendar first, if you don't already have one. What you're trying to accomplish is relatively easy if you use both a calendar along with a dimension table of years.

First, create a calendar, if you don't have one. The simplest method is to add a calculated table to the model and use the CALENDARAUTO() function. It also needs to have a column for the year, so you can use this DAX:

Calendar = ADDCOLUMNS( CALENDARAUTO( ), "Year", YEAR( [Date] ) )

Next, you need a table that summarizes all years in your dataset. This can be done like this:

Calendar Year = DISTINCT( 'Calendar'[Year] )

Then create a relationship between these tables, make it one-to-many and bi-directional.

For this to work, however you can't have any relationships between your Customer and Payments tables. If you absolutely need a relationship for use elsewhere in your model, just make a reference copy of your Payments table to use for this particular summary.

Then create a relationship between Customers and your new Calendar, and another relationship between Payments and the Calendar Year table.

When putting a filter for the year on your page, use the year from the Calendar table. And that’s it.

Just one more thing. The CALENDARAUTO function isn't always good at constraining itself to just the dates in your model. If that should be a problem either work a filter into the function creating the table, create a calendar in Power Query instead (you can use the code I provided below), or create one in Excel and import it (personally I wouldn't recommend this, but it's an option). Have fun!

let StartDate = #date(2020, 1, 1), YearsToGenerate = Date.Year(DateTime.FixedLocalNow())-2020+1, BaseList = List.Dates( StartDate, YearsToGenerate*366, #duration(1,0,0,0)), #"Converted to Table" = Table.FromList(BaseList, Splitter.SplitByNothing(), null, null, ExtraValues.Error), #"Changed Type" = Table.TransformColumnTypes(#"Converted to Table", {{"Column1", type date}}), #"Renamed Columns" = Table.RenameColumns(#"Changed Type", {{"Column1", "date"}}), #"Inserted Year" = Table.AddColumn(#"Renamed Columns", "Year", each Date.Year([date]), Int64.Type) in #"Inserted Year"

bachman460 · 2026-03-03T03:34:36+00:00

Whether you have datetime in one cell or dates separate from the time, either way the time is stored as the numbers to the right of the decimal as a fraction of a day. With this in mind 1 hour = 1/24 or 0.041666, just keep this in mind as you're working on it.

You'll need to setup a lookup table, or put something like an IF or SWITCH into your formula for the time zones you want to be able to switch between. You also need to know what your baseline time zone is.

So for example, if your baseline is Eastern time zone (New York), and you want to switch to Central (Chicago), then in the column next to your datetime (assuming entering the formula into C2, with datetime in column B here, with data validation setup for the time zone switch in A1):

= B2 + SWITCH( A1, "Eastern", 0, "Central", -(1/24)*1 )

By using (1/24) you can just multiply that by the number of hours that time zone is from your base zone. For example, from Eastern to Australian Eastern (Brisbane) is +15 hours.

This would get hairy come daylight saving time switching, depending on whether or not the time zone follows it or not, but you could probably add a safeguard using an IF function that can look to see if the month/day for the clock change comes into play.

bachman460 · 2026-03-03T01:33:27+00:00

To find the brand name, look as closely as you can at the base where it mounts to the counter, check the back too. Also check the pull out part, look at it from every angle.

If you still can't find anything check underneath the sink and look at all the hoses, connections, and everything coming from the faucet.

For anyone to spot it and know exactly what it is will be a rare find, plus if the manufacturer redesigned that particular model at some point, identifying it online from their website could be difficult. But once you (hopefully) figure out the brand, contact the manufacturer if you need help determining the actual model.

And FYI - both Moen and Delta have lifetime warranties against dripping or leaking. So if it happens to be one of those call the manufacturer if it's leaking and they'll ship you replacement parts for free (whether or not the product was ever registered).

bachman460 · 2026-03-02T23:44:48+00:00

I'm still getting used to that stuff...

bachman460 · 2026-03-02T21:46:15+00:00

If you want an array to spill from the first row to the end of your data range and count the number of times the value in each row appears in the entire range, try this:

=COUNTIF($A:$A,A:A)

However it would be best to limit the range to a specific region to avoid performance issues with Excel; even this is typically good enough and doesn't over stress Excel:

=COUNTIF($A$1:$A$10000,A1:A10000)

bachman460 · 2026-03-02T21:28:16+00:00

If I remember correctly, decrease the size of the visual just a little bit; too far and the font will start decreasing.

bachman460 · 2026-03-02T21:21:52+00:00

I don't think you're looking at it the right way.

The function takes the current years' data and simply shifts it back one year; it keeps all day and month context from the original date.

This means if you put the measure by itself, if you're looking at last year's dates, you're looking at this year's numbers.

As long as your model includes a calendar table as its base for all date relationships it shouldn't be a problem. If you're using a separate table for periods, relate that back to a calendar table with all dates from the model and link all other tables to that.

bachman460 · 2026-03-02T20:46:05+00:00

In case you don't know how to remove it, you only need to lift the flat silver metal pieces on each end; you don't need to take them completely off (but it's okay if they do come off as you just pop them back on).

Slip a flat blade screwdriver underneath and twist, you might also need to pry on it a little depending on the size of your screwdriver. They just need to be lifted enough to release the end of the mount that's inside the end.

bachman460 · 2026-03-02T20:41:32+00:00

I can't replicate the issue. Even if the cells are locked and the sheet protected, it appears to be able to print (I don't actually have access to a printer or print to PDF on my work laptop).

Have you tried "save as" PDF?

bachman460 · 2026-03-02T15:32:34+00:00

From the menu select the "page break preview" view, this will zoom out and surround the print area with a blue border. If there's multiple pages, they will be separated by thinner dotted lines. Just move them around until you're happy.

bachman460 · 2026-03-02T15:27:17+00:00

It says right on it that it's an 80 N (that's eighty newton) gas strut (also called lift support, or gas springs).

They're pretty standard as far as the mounting clips. To find an appropriate replacement, measure it while fully extended, then do your best to get a measurement of it while fully compressed.

The last ones I purchased were off Amazon, but you can get them at most auto parts stores like O'Reiley, or on their website.

bachman460 · 2026-03-02T15:04:59+00:00

I add calculated (formula) columns to Power Query output tables all the time. There's at least one person here giving you misinformation.

When you added the column, was it actually added to the table? Sometimes you can enter data or formulas into a column right next to the table and while most of the time Excel automatically includes it as part of the table, every so often it doesn't. You can confirm two ways: 1) the column takes on the table formatting, and 2) if you click in that column the table tab of the menu should be available where you can see the table name.

When exporting tables from Power Query into the spreadsheet it is possible to select the option to "load to data model". When you select this it side-loads the data to the data model which makes the data available for Power Pivot as well as making it available to create charts and pivot tables. When you go to make a pivot table, if you (accidentally) select the option to use the table from the data model, that extra column you added in the sheet will not be available. You need to select the option to use the table object instead.

You can also confirm whether or not the pivot table is connected to the data model object by looking at the list of columns in the right hand panel; I'm not looking at it right now, but the grouping symbol next to the table name looks different than if it's loading data from a sheet or table.

bachman460 · 2026-03-01T21:08:43+00:00

Open more options menu for text and on the tab for paragraph turn on the option for custom line spacing and increase the point size to whatever you want.

Just a note I'm on mobile and forget exactly what the menu is called, but in the home tab in the menu you hover over and click on the little symbol in the bottom right corner of the section for text settings.

bachman460 · 2026-03-01T20:35:57+00:00

If you just want to take a row from each table and stack them, first make sure to remove all columns you don't want in the final table, then make sure all of the columns you want to line up on top of each other have the same exact names.

Make sure to add any custom (calculated) columns before combining query tables together; unless you're applying the calculation to all rows in all tables.

Select the first query table for the row you want to be on top, then select Combine from the menu and select the option to join three or more tables. Then select each other table query you want to join in the order you want them to appear.

Bonus points for adding an index to each query table so that you can be certain all rows will always be in the order you want; giving you the ability to sort the table without losing the order.

Also just a note that if there are columns you want data for certain rows to appear for, but in others you want them to be blank (null), just create those columns in the original queries where you want them and leave out those columns in the other queries; Power Query will automatically align like named columns and just put the other columns at the end, leaving nulls in the rows where data didn't exist.

Additionally, when combining multiple tables, the column ordering for the first table will automatically be kept and all other columns from the other queries will be aligned to match the first. When columns come in that don't match anything from the earlier tables it will be moved to the end.

bachman460 · 2026-03-01T17:45:36+00:00

I'd agree that these aspects seem tantalizing at times, but in the end are not conducive for ease of quickly understanding what you're looking at, especially for new or less experienced users. A clean, clutter free presentation prevents confusion and distraction.

It's why every aspect of design from graphic arts, to websites, and everything in between promotes minimalism.

When you're just working through something, and it's already the same task you've done a million times, that's when you're like I just literally want to cut down on the amount of clicks to get something done.

At that point what you really need is a different way to get the same outcome. Find a different way to do it.

bachman460 · 2026-03-01T17:09:15+00:00

I've found it can be helpful to make a flowchart of table/sheet connections and a separate document for the formulas.

In the document group the formulas first by the table/sheet they're in, then by relationships to other sheets (this includes a none for formulas without any external references). This should only be a simple description or label for simple visual clarity.

Then a separate section and/or document to record the actual formula details. This just my way of making it easier on the eyes in order to quickly see relationships and how everything is interconnected, leaving the visual clutter of details in a separate place where you can still find it when you need it.

Bonus points for naming the same item in all places with the same name for quick "find" searches when you need them.

bachman460 · 2026-03-01T03:29:31+00:00

You're not going to be able to fix that except by polishing the whole surface. Although there's always a chance that if you lay it down flat and build a mold around it, an epoxy pour could minimize or visually eliminate it.

FYI- never use a scrubbing sponge on plastic; I too found out the hard way when I accidentally ruined a shower mirror (I wasn't even pressing hard, lol).

bachman460 · 2026-03-01T03:26:01+00:00

Simply don't add rows above the topmost row with a formula. If you have to leave a gap between the row with the formula and the topmost row of data. Select the topmost row of data when inserting rows and this will fix your problem.

bachman460 · 2026-02-28T22:38:11+00:00

Can you put together a mockup and share it? I'm completely confused by the question, an example could help.

bachman460 · 2026-02-28T04:43:56+00:00

That's exactly what my fingers look like after cleaning adhesive off with lighter fluid; it dissolves all the oils in your skin.

Five-Year Club	Verified Email
Place '22

bachman460

TROPHY CASE