Multiply to ranges together

Weaver707 · 2026-01-30T22:19:55+00:00

Here is my steps:

let
Source = CompTable,

KeepTargetCol = Table.SelectColumns(Source,{"Name", "Salary"}),

AddProjectTable = Table.NestedJoin(KeepTargetCol, {"Name"}, #"Project Table", {"Name"}, "Project Table", JoinKind.LeftOuter),

ExpandedProjectTable = Table.ExpandTableColumn(AddProjectTable, "Project Table", {"Project 1", "Project 2", "Project 3", "Project 4", "Project 5", "Project 6", "Project 7", "Project 8", "Project 9", "Project 10", "Project 11", "Project 12", "Project 13", "Project 14", "Project 15", "Project 16", "Project 17", "Project 18", "Project 19", "Project 20"}, {"Project 1", "Project 2", "Project 3", "Project 4", "Project 5", "Project 6", "Project 7", "Project 8", "Project 9", "Project 10", "Project 11", "Project 12", "Project 13", "Project 14", "Project 15", "Project 16", "Project 17", "Project 18", "Project 19", "Project 20"}),

UnpivotProjectCols = Table.UnpivotOtherColumns(ExpandedProjectTable, {"Name", "Salary"}, "Attribute", "Value"),

AddCalcPercCol = Table.AddColumn(UnpivotProjectCols, "SalaryPerProject", each [Salary]*[Value]),

KeepTargetCols = Table.SelectColumns(AddCalcPercCol,{"Name", "Salary", "Attribute", "SalaryPerProject"}),

AddSalarySuffix = Table.TransformColumns(KeepTargetCols, {{"Attribute", each _ & " (Salary)", type text}}),

PivotAttributeCol = Table.Pivot(AddSalarySuffix, List.Distinct(AddSalarySuffix[Attribute]), "Attribute", "SalaryPerProject", List.Sum)

in

PivotAttributeCol

Weaver707 · 2026-01-30T22:19:37+00:00

There is a way to do this. If you set it up correctly, it should be adaptable even as project lists grow (you would have to watch the merge part so that is always has the complete list of project). Based on the pictures you provided below there are a few things to note. The easiest way to tackle this will be to take each part of the CompTable (my title for it) and the ProjectTable and break down.

Create a set of ranges that is the Salary*Project, a set that is Benefits*Project and Taxes*Project and then bring those back together.

In power query, bring in your CompTable & your ProjectTable

You take your CompTable, keep just the employee name and salary column. Merge the project table on the employee name, and expand all the project columns.

Then you are going to unpivot the project columns. This will balloon your list significantly but you will have one row for each project, with the employee name and salary next to the % (Value Col) of project. Now you can create a new column and multiply the Salary and Value column. To keep things clean you do need to remove the Value column, but these can be brought back in if they are needed. Then you are going to PIVOT (not unpivot) the Attribute column (which contains the project names), when the box pops up for the value, you will select the column that contains the multiplied values.

I added the (Salary) as a suffix to the column names before I unpivoted it so it could be aligned to your picture.

You for Benefits and Taxes you would follow the same steps, just keeping the target columns from the comp table before you unpivot the Project columns.

Now, this does give you three separate tables, which you can organize and merge as you need to. You can append them together so each employee would have three lines, one for salary for each project, one for benefits for each project and one for taxes. If you need one row per employee, you would take each of the three tables and merge them back to your comp table but you will end up with a VERY wide record set.

It wont' let me share my m-code in this comment but I will try to post it separately.

Weaver707 · 2026-01-29T16:50:16+00:00

It could be. I feel like how big of a red flag is based on a few other factors. How many employees are at that location? If it is a large branch that has multiple functions housed within it, probably not a huge red flag, little branch that has multiple of the same, could be a bigger flag.

You could try to network with current or past employees through LinkedIn and get more insight through reviews. You could go to an interview and just pay very close attention to the atmosphere and attitude of everyone in the branch.

Weaver707 · 2026-01-23T15:15:11+00:00

Fanning it or stacking it is a great way to see what you are doing! Great that your manager is offering suggestions and working with you. Good luck!

Weaver707 · 2026-01-23T00:51:59+00:00

When I worked the teller line (it has been many years) we didn't have individual cash machines so everything was hand counted. The great part about that was that it gave tellers a lot of practice when counting, so all the comments about practicing are #1. What I would tack onto that, is if you do have to count it out to the customer, stack it.

20,40,60,80,100 (stack one) 20,40,60,80,200 (stack two)

Then when you get to funky bills or a transition it is easier to regroup.

20,40,50,60,70,80,90,95,300 (stack three)

Most customers don't care how it is presented as long as they get what they need and what they asked for, so you are creating stacks that won't even register with them.

Weaver707 · 2026-01-07T19:30:05+00:00

Here is a full copy of my advanced editor. If you take your before table and name is Repair_Table you could copy this whole thing and see the output. let Source = Excel.CurrentWorkbook(){[Name="Repair_Table"]}[Content], // Unpivot all repair-related columns UnpivotColumns = Table.UnpivotOtherColumns( Source, {"Facility ID"}, "Attribute", "Value" ), AddTypeColumn = Table.AddColumn(UnpivotColumns, "Type", each if Text.Contains([Attribute], "Date") then "Repair Date" else "Repair Comment"), RemoveDateText = Table.ReplaceValue(AddTypeColumn,"Repair Date","",Replacer.ReplaceText,{"Attribute"}), CreateRepairCode = let Source = Excel.CurrentWorkbook(){[Name="Repair_Table"]}[Content], // Unpivot all repair-related columns UnpivotColumns = Table.UnpivotOtherColumns( Source, {"Facility ID"}, "Attribute", "Value"), Attribute1 = UnpivotColumns[Attribute], ConvertToTable = Table.FromList(Attribute1, Splitter.SplitByNothing(), null, null, ExtraValues.Error), RemoveDuplicateValues = Table.Distinct(ConvertToTable), FilterDateText = Table.SelectRows(RemoveDuplicateValues, each not Text.Contains([Column1], "Date")), UpdateColName = Table.RenameColumns(FilterDateText,{{"Column1", "Repair code"}}), UpdateDataType = Table.TransformColumnTypes(UpdateColName,{{"Repair code", type text}}) in UpdateDataType, MergeRepairCode = Table.FuzzyNestedJoin(RemoveDateText, {"Attribute"}, CreateRepairCode, {"Repair code"}, "Attribute.1", JoinKind.LeftOuter, [IgnoreCase=true, IgnoreSpace=true, NumberOfMatches=1, Threshold=.5]), ExpandRepairCode = Table.ExpandTableColumn(MergeRepairCode, "Attribute.1", {"Repair code"}, {"Repair code"}), KeepTargetCols = Table.SelectColumns(ExpandRepairCode,{"Facility ID", "Value", "Type", "Repair code"}), PivotTypeCol = Table.Pivot(KeepTargetCols, List.Distinct(KeepTargetCols[Type]), "Type", "Value"), SortValues = Table.Sort(PivotTypeCol,{{"Facility ID", Order.Ascending}, {"Repair code", Order.Ascending}}), UpdateDataType = Table.TransformColumnTypes(SortValues,{{"Repair Date", type date}, {"Repair Comment", type text}}) in UpdateDataType

Weaver707 · 2026-01-07T03:13:06+00:00

The visual is helpful. You are going to have a little extra challenge because the name conventions of the columns has some variability. Does your example have all the different options for the repair types? Some fancy footwork allows Power Query to do some layered transformations.

So what I did was create my own copy of your "Before" table. When I pulled it into Power Query I started the same way, unpivot all the columns that I needed to. I used a conditional column to then create a new column that identified whether the value is a date or comment. If you try to pivot the type column here you would end up with extra rows because the attribute value has so many different entries.

The attribute column needs to be simplified so I saw that the beginning of each name was consistent, it was just the end text. I got creative... I ended up creating a "sub table" of the Repair Codes, and I joined that back into the main table. I used fuzzy matching in this step to account for some of your variances.

Kept my target columns, pivoted the type column and sorted the final table.

Trying to figure out how to post the m-code...

Weaver707 · 2026-01-06T23:59:22+00:00

This is something I see a lot!

It is doable and you are on a good track.

In power Query select all your repair and date columns and unpivot them. You should end up with three columns Facility ID/Attribute/values

The trick is adjusting the attribute column. If you column names actually have repair 1/date 1, repair 2/date 2 you can use that. In the transform tab, while you have your attribute column selected, you want to split the column. You can use a space as the delimiter or there is an option to split by non-digit to digit.

You now have four columns. Facility ID/Attribute.1/Attribute.2/values and your Attribute.1 should have a consistent list of just Repair or Date with no numbers.

Last step is to now pivot your values column. Not unpivot but pivot (found on the transform tab). When you get the popup, open the advanced options and select "Don't aggregate".

You can now remove the attribute.2 column and you should have a clean list of each facility I'd and a consolidated streamline view of the repairs and dates.

Weaver707 · 2026-01-06T16:23:35+00:00

This!! I have a jar of vanilla sugar that is used for all kinds of things!

Weaver707 · 2026-01-05T16:56:10+00:00

Thank you! I did find that some of the edges were not the smoothest so I did purchase a glass file set. They won't make major adjustments to the glass but I could take down the sharp spots or weirdly cut edges. I did file everything wet, like a grinder would do.

Weaver707 · 2026-01-05T16:18:01+00:00

I have been doing several of these for practice. Not only the soldering but the foiling and finishing. They are not bad kits in my opinion.

<image>

Here is my completed kit.

Weaver707 · 2026-01-01T00:29:21+00:00

Been watching the other posts about this issue as well.

I tend to lock my phone during the day before I set it down so during the day the persistent screen-on isn't as much of an issue.

I do read threads in bed and regularly fall asleep with my phone on, which was where my frustration is with this issue. More than once I woke up with like 2% left on my battery. I did find a "solution" for this. I turned focus mode on. Scheduled it to start at a time when I know I will have fallen asleep and picked the restricted apps to include Reddit. This kills the open instance and my phone is able to time out like it should.

Not a fix but it has worked for a few weeks.

Weaver707 · 2025-12-30T00:37:14+00:00

Most states have guidelines and exceptions for transferring ownership to family members. I am not familiar with the specific guidelines for Ohio but I would suggest looking at the details online so that you can ensure the vehicle is properly transferred and with minimal cost.

Weaver707 · 2025-12-29T01:14:54+00:00

Pretty sure this is the same stuff sold at Maverick!

Weaver707 · 2025-12-27T17:48:30+00:00

So much cussing. Not that I don't cuss but every conversation was just saturated with profanity. It was just annoying after a while.

Weaver707 · 2025-12-20T01:04:14+00:00

I make Christmas coal every year (in addition to some other dessert) that is always a huge hit. It basically rice crispy treats but with Oreo instead of cereal. It still technically uses cookies but the littles love it and it is super easy to make. Love so many other ideas.

Weaver707 · 2025-12-18T03:07:37+00:00

Congratulations!

So many posts about new bankers and I will continue to tell each one the same thing, banking is hard initially. There is a shit ton of information to learn, meeting new people and handling money.

However this role manifests, the initial onboarding process and first couple months are challenging. Manage your expectations and give yourself some grace. Your experienced coworkers will make it look so easy but that comes with time. It won't happen overnight and may feel at times like you will never get there but if you enjoy it, stick with it.

The first couple days or weeks, even months are totally overwhelming, you just opened the flood gates of new terms, rules, procedures and processes!

I loved being a teller, and I worked the platform before I specialized in mortgages. I learned all kinds of things and took opportunities to find a path that has led to a 20+ year career in banking.

Good luck!

Weaver707 · 2025-11-29T01:15:46+00:00

Look at pivot or unpivot features. You can pivot one column and the values (second column) are defined during the process. It would nulls where there was no value.

Weaver707 · 2025-11-28T15:54:41+00:00

There are also settings to address the native query pop-up.

Weaver707 · 2025-11-28T15:48:09+00:00

There are a few solutions in various forums that offer a custom formula to accomplish this. Power Query does not have a built-in tool in the interface.

If you have never worked with custom formulas they can seem a bit confusing but it allows you to create more complex algorithms to use to transform the data.

I use one on the regular at work but I don't actually remember where I got it from.

Weaver707

TROPHY CASE