Q:

Power Query: Adding characters to a set limit across several columns/rows

Very new to PQ, and I'm pretty sure it can do what I need in this situation, but I need help figuring out how to get there.
I have a timesheet report with 20 columns covering 50 rows that will need to be formatted to a word doc for uploading into a separate system. The original data in the cells range from 0 to any negative 2 digit number (ex: "-20"), but they need to be formatted to a seven-character set ending in ".00".
Examples: 
0 will need to become "0000.00"
-4 will need to become "-004.00"
-25 will need to become "-025.00"
I think I should be able to use the text.insert function, but I'm not familiar enough with M Language to get it to do what I want it to do. 
Any solutions/suggestions?

A:

Here's my previous answer revisited...set up to use a function. You can just invoke the function once for each column you want to reformat. You'll just pass the name of the column you want to reformat to the function as you invoke the function each time.
Create a new blank query:

Open the new query in Advanced Editor and highlight everything in it:

Paste this over the highlighted text in the Advanced Editor:
let 

    FormatIt = (SourceColumn) => 

let

    Base = Number.Round(SourceColumn,2)*.01,
    Source = try Text.Start(Text.Range(
                if Base < 7 then Text.From(Base) & "001" else 
                Text.From(Base),0,7),2) & Text.Range(Text.Range(
                    if Base < 7 then Text.From(Base) & "001" else 
                    Text.From(Base),0,7),3,2) & "." & Text.End(Text.Range(
                        if Base < 7 then Text.From(Base) & "001" else 
                        Text.From(Base),0,7),2)
             otherwise "0000.00"

in
    Source

in 

    FormatIt

...and click Done.
You'll see a new function has been created and listed in the Queries list on the left side of the screen.
Then go to your query with the columns you want to reformat (click on the name of your query that has the numbers you want to change in it, on the left side of the screen) and...
Click Invoke Custom Function

And fill out the pop-up like this:

- You can make up a different New column name than Custom.1.
- Function Query is the name of your query you are calling (the one you just created when you pasted the code)...for me, it's called Query1.
- Source Column is the column with the numbers you want to format. 
...and click OK.
You can invoke this function once for each column. It will create a new formatted column for each.

