Delimiter

[RVGypsy]

I have a column of cells with some cells having 5 number and some having 6. None have the correct format that I need them in and all need to be 6 numbers long. I need to add dashes in between so I have 3 sets of 2 number and have the finished column as a ext column. I know I can do this in excel with the special format of 00-00-00 but the column formated that way screws up the next step in my process. I tried to use a conditional column but it wants to put them in date format 00/00/00. In the example below I am referring to the first column in both. This is the last step i need for my report and once it is done I need to export convert this back to a range instead of a table. I dont want to have to rewrite this column everytime I need it bacuse it is currently 287 lines long.

What I haveWhat I need

[RVGypsy]

Well That ends up like I want it to but not all the way. I need it to pull from the first column and change that number (watever number it may be) into what I need. I currently have 287 rows of diferrent specific number to do this to. starting at 11530 where I need to add a leading zero and the dashes to 999910 where all I need are the dashes.

[BA_Pete]

Hi @RVGypsy ,

Can you give me a bit more detail around what part of this isn't working please?

Are there certain values that appear in your first column that this isn't working with, or are you getting errors on some values?

It appears to work fine on the values you've provided above:

If you're able to share the entire list of your first column values I can test my solution on the actual data and fix anything that doesn't work.

Pete

[RVGypsy]

Here is the entire process so far, I need the code column to be 00-00-00 for each specific number already in the column.

Source = Excel.CurrentWorkbook(){[Name="PM_Budget"]}[Content],
#"Add Cost Type" = Table.AddColumn(Source, "Cost Type", each if [Code] = 11520 then "O" else if [Code] = 11525 then "O" else if [Code] = 11530 then "O" else if [Code] = 11535 then "O" else if [Code] = 11540 then "O" else if [Code] = 11810 then "O" else if [Code] = 11820 then "O" else if [Code] = 11825 then "O" else if [Code] = 11830 then "O" else if [Code] = 12001 then "O" else if [Code] = 12005 then "L" else if [Code] = 12010 then "O" else if [Code] = 12015 then "O" else if [Code] = 12015 then "O" else if [Code] = 12016 then "O" else if [Code] = 12020 then "HP" else if [Code] = 12025 then "O" else if [Code] = 12035 then "O" else if [Code] = 12045 then "O" else if [Code] = 12505 then "L" else if [Code] = 12510 then "O" else if [Code] = 12515 then "O" else if [Code] = 12516 then "O" else if [Code] = 12520 then "HS" else if [Code] = 12525 then "O" else if [Code] = 12535 then "O" else if [Code] = 12545 then "O" else if [Code] = 12910 then "L" else if [Code] = 13115 then "O" else if [Code] = 13120 then "O" else if [Code] = 13121 then "O" else if [Code] = 13122 then "O" else if [Code] = 13125 then "O" else if [Code] = 13130 then "O" else if [Code] = 13216 then "O" else if [Code] = 13529 then "O" else if [Code] = 13553 then "O" else if [Code] = 14126 then "O" else if [Code] = 14523 then "S" else if [Code] = 15113 then "O" else if [Code] = 15129 then "O" else if [Code] = 15136 then "O" else if [Code] = 15213 then "O" else if [Code] = 15215 then "O" else if [Code] = 15219 then "O" else if [Code] = 15416 then "E" else if [Code] = 15626 then "O" else if [Code] = 15810 then "O" else if [Code] = 15910 then "O" else if [Code] = 17105 then "O" else if [Code] = 17110 then "O" else if [Code] = 17115 then "O" else if [Code] = 17120 then "O" else if [Code] = 17413 then "O" else if [Code] = 17419 then "O" else if [Code] = 17423 then "S" else if [Code] = 17836 then "O" else if [Code] = 19910 then "O" else if [Code] = 19915 then "O" else if [Code] = 19920 then "O" else if [Code] = 980510 then "O" else if [Code] = 980520 then "O" else if [Code] = 981010 then "O" else if [Code] = 981015 then "O" else if [Code] = 981015 then "O" else if [Code] = 981020 then "O" else if [Code] = 981025 then "O" else if [Code] = 981510 then "O" else if [Code] = 981515 then "O" else if [Code] = 981520 then "O" else if [Code] = 981525 then "O" else if [Code] = 981530 then "O" else if [Code] = 989910 then "O" else if [Code] = 989999 then "O" else if [Code] = 992513 then "O" else if [Code] = 992516 then "O" else if [Code] = 998005 then "L" else if [Code] = 998010 then "O" else if [Code] = 998015 then "O" else if [Code] = 998020 then "O" else if [Code] = 998025 then "O" else if [Code] = 998026 then "O" else if [Code] = 998030 then "O" else if [Code] = 998035 then "O" else if [Code] = 998040 then "O" else if [Code] = 999910 then "O" else if [Code] = 999915 then "O" else if [Code] = 999920 then "O" else if [Code] = 999925 then "O" else if [#"Labor/Sub."] > 0 then "S" else if [Materials] > 0 then "M" else if [Equipment] > 0 then "E" else if [Bond] > 0 then "B" else if [Allowance] > 0 then "A" else null),
#"Grouped Rows" = Table.Group(#"Add Cost Type", {"Code", "Cost Type"}, {{"Budget Amount", each List.Sum([PM Budget]), type number}}),
#"Add Description" = Table.AddColumn(#"Grouped Rows", "Description", each if [Code] = 11520 then "Legal Fees" else if [Code] = 11525 then "Taxes" else if [Code] = 11530 then "Builders Risk " else if [Code] = 11535 then "Bonding Requirements" else if [Code] = 11540 then "General Liability Insurance" else if [Code] = 11810 then "PCS PM Allocation" else if [Code] = 11820 then "APM Allocation" else if [Code] = 11825 then "PE Allocation" else if [Code] = 11830 then "Accounting Allocation" else if [Code] = 12001 then "PM Cost Allocation" else if [Code] = 12005 then "PM Salaries" else if [Code] = 12010 then "PM Per Diem / Meals" else if [Code] = 12015 then "PM Vehicle Allowance" else if [Code] = 12016 then "PM Gas Expense" else if [Code] = 12020 then "PM Lodging" else if [Code] = 12025 then "PM Airline Travel" else if [Code] = 12030 then "PM Bonus" else if [Code] = 12035 then "PM Benefits" else if [Code] = 12045 then "PM Cell Phone" else if [Code] = 12505 then "Super Salaries" else if [Code] = 12510 then "Super Per Diem / Meals" else if [Code] = 12515 then "Super Vehicle Allowance" else if [Code] = 12516 then "Super Gas Expense" else if [Code] = 12520 then "Super Lodging" else if [Code] = 12525 then "Super Airline Tavel" else if [Code] = 12530 then "Super Bonus" else if [Code] = 12535 then "Super Benefits" else if [Code] = 12545 then "Super Cell Phone" else if [Code] = 12910 then "General Superintendent Expenses" else if [Code] = 13005 then "Self Performing Labor" else if [Code] = 13010 then "Labor Per Diem / Meals" else if [Code] = 13015 then "Labor Vehicle Allowance" else if [Code] = 13016 then "Labor Gas Expense" else if [Code] = 13020 then "Labor Lodging" else if [Code] = 13115 then "Construction Plan" else if [Code] = 13120 then "Construction Plan Shipments" else if [Code] = 13121 then "Bid Travel Expenses" else if [Code] = 13122 then "Bid Meal Expenses" else if [Code] = 13125 then "Postage / Freight / Overnite" else if [Code] = 13130 then "Field Office Expense" else if [Code] = 13216 then "Construction Progress Schedule" else if [Code] = 13529 then "Jobsite Safety" else if [Code] = 13553 then "Security" else if [Code] = 14126 then "Permits" else if [Code] = 14523 then "Testing & Inspecting Service" else if [Code] = 15113 then "Temp Electric" else if [Code] = 15129 then "Temp Gas" else if [Code] = 15136 then "Temp Water" else if [Code] = 15213 then "Jobsite Trailer" else if [Code] = 15215 then "Jobsite Storage" else if [Code] = 15219 then "Portable Toilet" else if [Code] = 15416 then "Equipment Rental" else if [Code] = 15626 then "Temp Fencing" else if [Code] = 15810 then "Project Identification" else if [Code] = 15910 then "Tools" else if [Code] = 17105 then "Architect Design Fees" else if [Code] = 17110 then "Civil Engineer Fees" else if [Code] = 17115 then "Structural Engineer Fees" else if [Code] = 17120 then "Mechanical Engineer Fees" else if [Code] = 17413 then "Geberal Labor" else if [Code] = 17419 then "Dumpster" else if [Code] = 17423 then "Final Cleaning" else if [Code] = 17836 then "Warranties" else if [Code] = 19910 then "Misc Overhead Costs" else if [Code] = 19915 then "Project IT" else if [Code] = 19920 then "General Conditions Contingency" else if [Code] = 22110 then "Survey" else if [Code] = 23210 then "Geotechnical Investigations" else if [Code] = 24110 then "Demolition" else if [Code] = 32110 then "Reinforcing" else if [Code] = 33010 then "Cast-In-Place Concrete" else if [Code] = 33110 then "Structural Concrete / Footers" else if [Code] = 33510 then "Concrete Finishing" else if [Code] = 33533 then "Stamped Concrete" else if [Code] = 35413 then "Gypsum Cement Underlayment" else if [Code] = 38213 then "Concrete Core Drilling" else if [Code] = 42210 then "CMU Block" else if [Code] = 42220 then "Above Ground Masonry Unit" else if [Code] = 42310 then "Glass Masonry Unit" else if [Code] = 44310 then "Stone Masonry" else if [Code] = 47110 then "Manufactured Brick Masonry" else if [Code] = 51210 then "Structural Steel" else if [Code] = 55110 then "Metal Stairs" else if [Code] = 55210 then "Metal Railings" else if [Code] = 55310 then "Metal Gratings" else if [Code] = 55910 then "Metal Specialties" else if [Code] = 57510 then "Decorative Formed Metal" else if [Code] = 61110 then "Rough Carpentry" else if [Code] = 61210 then "Pre-Fab Panels" else if [Code] = 61710 then "Trusses / I-Joists" else if [Code] = 62210 then "Millwork / Casework" else if [Code] = 62215 then "Finish Carpentry" else if [Code] = 72110 then "Thermal Insulation" else if [Code] = 72410 then "EIFS" else if [Code] = 72616 then "Vapor Mitigation" else if [Code] = 73110 then "Shingles & Shakes" else if [Code] = 73210 then "Roof Tiles" else if [Code] = 74610 then "Siding" else if [Code] = 75110 then "Built-Up Bituminous Roof" else if [Code] = 75210 then "Modified Bituminous Roof" else if [Code] = 75310 then "Elastomeric Membrane Roof" else if [Code] = 75410 then "TPO / PVC Roof" else if [Code] = 76110 then "Metal Roof" else if [Code] = 76510 then "Flashing" else if [Code] = 77210 then "Roof Accessories" else if [Code] = 78410 then "Fireproofing" else if [Code] = 79210 then "Joint Sealants" else if [Code] = 79215 then "Caulking & Sealants" else if [Code] = 81110 then "Doors, Frames & Hardware" else if [Code] = 84229 then "Automatic Entrances" else if [Code] = 84310 then "Aluminum Storefronts" else if [Code] = 85010 then "Window Units" else if [Code] = 88110 then "Glass Glazing / Mirrors" else if [Code] = 92110 then "Drywall" else if [Code] = 92216 then "Metal Stud" else if [Code] = 92410 then "Stucco" else if [Code] = 93010 then "Tiling" else if [Code] = 95110 then "Acoustical Ceilings" else if [Code] = 96310 then "Specialty Flooring" else if [Code] = 96410 then "Wood Fooring" else if [Code] = 96510 then "Resilient / Rubber Flooring" else if [Code] = 96519 then "VCT Flooring" else if [Code] = 96710 then "Fluid Applied Flooring" else if [Code] = 96810 then "Carpeting" else if [Code] = 97210 then "Wall Coverings" else if [Code] = 97710 then "Special Wall Surfaces" else if [Code] = 97733 then "FRP" else if [Code] = 99110 then "Painting" else if [Code] = 101410 then "Signage" else if [Code] = 102210 then "Partitions" else if [Code] = 102610 then "Wall & Door Protection" else if [Code] = 102810 then "Toilet, Bath, Laundry Accessories" else if [Code] = 102819 then "Tub & Shower Surrounds" else if [Code] = 103110 then "Fireplaces" else if [Code] = 104410 then "Fire Protection Specialties" else if [Code] = 105110 then "Lockers" else if [Code] = 107313 then "Awnings" else if [Code] = 107510 then "Flagpoles" else if [Code] = 108210 then "Roof Equipment Screens" else if [Code] = 111210 then "Parking Control Equipment" else if [Code] = 111310 then "Loading Dock Equipment" else if [Code] = 114110 then "Food Storage Equipment" else if [Code] = 114210 then "Food Preperation Equipment" else if [Code] = 114410 then "Food Cooking Equipment" else if [Code] = 115210 then "Audio-Visual Equipment" else if [Code] = 122110 then "Window Blinds" else if [Code] = 123510 then "Specialty Casework" else if [Code] = 123610 then "Stone Countertops" else if [Code] = 124610 then "Furnishing Accessories" else if [Code] = 124810 then "Rugs & Mats" else if [Code] = 131110 then "Swimming Pool Accessories" else if [Code] = 133410 then "Pre-Engineered Metal Building" else if [Code] = 142810 then "Elevators" else if [Code] = 143110 then "Escalators" else if [Code] = 143210 then "Moving Walks" else if [Code] = 149133 then "Laundry Chutes" else if [Code] = 211310 then "Sprinkler System" else if [Code] = 220610 then "Interior Plumbing System" else if [Code] = 221323 then "Grease Trap" else if [Code] = 230593 then "Test & Balance" else if [Code] = 230610 then "HVAC Systems" else if [Code] = 231123 then "Natural Gas Piping" else if [Code] = 231313 then "Fuel Systems" else if [Code] = 233813 then "Ventilation Hoods" else if [Code] = 237310 then "Split Systems" else if [Code] = 237410 then "Package HVAC Equipment" else if [Code] = 238310 then "Radiant Heating Systems" else if [Code] = 238410 then "Humidity Control Units" else if [Code] = 260610 then "Interior Electrical Distribution" else if [Code] = 261310 then "Switchgear" else if [Code] = 262010 then "Low-Voltage Distribution" else if [Code] = 264113 then "Lightening Protection" else if [Code] = 265110 then "Interior Lighting" else if [Code] = 265610 then "Exterior Lighting" else if [Code] = 265613 then "Light Poles / Bases" else if [Code] = 270510 then "Communications" else if [Code] = 270511 then "Audio-Visual Cabling" else if [Code] = 281310 then "Access Controls" else if [Code] = 281610 then "Security Systems" else if [Code] = 283110 then "Fire Detection & Alarm" else if [Code] = 310610 then "Mobilization" else if [Code] = 311110 then "Clear & Grub" else if [Code] = 312210 then "Grading" else if [Code] = 312216 then "Finish Grading" else if [Code] = 312316 then "Excavation" else if [Code] = 312317 then "Rock Removal" else if [Code] = 312319 then "Dewatering" else if [Code] = 312323 then "Export / Import Fill" else if [Code] = 312510 then "Erosion & Sediment Control" else if [Code] = 312573 then "Storm Water Management" else if [Code] = 313110 then "Termite Treatment" else if [Code] = 313210 then "Soil Stabilization" else if [Code] = 316210 then "Driven Piles" else if [Code] = 320610 then "Sidewalk, Driveway & Patio" else if [Code] = 321123 then "Base Course Drainage Layer" else if [Code] = 321210 then "Asphalt Paving" else if [Code] = 321236 then "Seal Coat" else if [Code] = 321310 then "Concrete Paving" else if [Code] = 321410 then "Unit Paving" else if [Code] = 321610 then "Curbs & Gutters" else if [Code] = 321713 then "Parking Bumbers" else if [Code] = 321723 then "Pavement Markings" else if [Code] = 321733 then "Parking Signage" else if [Code] = 323110 then "Fences & Gates" else if [Code] = 323210 then "Retaining Walls" else if [Code] = 323513 then "Site Screens & Louvers" else if [Code] = 328010 then "Irrigation" else if [Code] = 329110 then "Landscape" else if [Code] = 331110 then "Fire Water Distribution" else if [Code] = 331115 then "Domestic Water Distribution" else if [Code] = 331611 then "Water Wells" else if [Code] = 333110 then "Sanitary Sewer Utility" else if [Code] = 333413 then "Septic Systems" else if [Code] = 334110 then "Storm Piping" else if [Code] = 334910 then "Underground Retention" else if [Code] = 335110 then "Natural Gas Distribution" else if [Code] = 337110 then "Primary Electric Service" else if [Code] = 337115 then "Secondary Electric Service" else if [Code] = 338110 then "Communications Site Work" else if [Code] = 344116 then "Traffic Control" else if [Code] = 980510 then "Winter Conditions" else if [Code] = 980520 then "Winter Conditions Temp Heat" else if [Code] = 981010 then "Misc Site Costs" else if [Code] = 981015 then "Misc Building Costs" else if [Code] = 981020 then "Contingency" else if [Code] = 981025 then "Allowance" else if [Code] = 981510 then "Theft" else if [Code] = 981515 then "Damage" else if [Code] = 981520 then "Misuse" else if [Code] = 981525 then "Fines & Penalties" else if [Code] = 981530 then "Covid-19" else if [Code] = 989910 then "Punch Crew Costs" else if [Code] = 989999 then "Job Cost Clearing" else if [Code] = 992513 then "Pre-Bid Meetings" else if [Code] = 992516 then "Pre-Proposal Meetings" else if [Code] = 998005 then "Estimating Personnel Labor" else if [Code] = 998010 then "Estimating Personnel Benefit" else if [Code] = 998015 then "Estimating Personnel Bonus" else if [Code] = 998020 then "Estimating Personnel Meals" else if [Code] = 998025 then "Bid Plans" else if [Code] = 998026 then "Take-Off" else if [Code] = 998030 then "Bid Plan Shipping" else if [Code] = 998040 then "Refundable Deposit" else if [Code] = 999910 then "Reimbursable Permits" else if [Code] = 999915 then "Reimbursable Tap Fees" else if [Code] = 999920 then "Reimbursable Owner Vendor" else if [Code] = 999925 then "Reimbursable Misc Work" else null),
#"Add Manual Calculation" = Table.AddColumn(#"Add Description", "Manual Calculation", each if [Budget Amount] > 0 then true else null),
#"Add Unit Qty" = Table.AddColumn(#"Add Manual Calculation", "Unit Qty", each if [Budget Amount] >= 0 then "" else null),
#"Add Unit of Measure" = Table.AddColumn(#"Add Unit Qty", "Unit of Measure", each if [Budget Amount] > 0 then "LS" else null),
#"Add Unit Cost" = Table.AddColumn(#"Add Unit of Measure", "Unit Cost", each if [Budget Amount] >= 0 then "" else null),
#"Add Start Date" = Table.AddColumn(#"Add Unit Cost", "Start Date", each if [Budget Amount] >= 0 then "" else null),
#"Add End Date" = Table.AddColumn(#"Add Start Date", "End Date", each if [Budget Amount] >= 0 then "" else null),
#"Add Curve" = Table.AddColumn(#"Add End Date", "Curve", each if [Budget Amount] >= 0 then "" else null),
#"Filtered Rows" = Table.SelectRows(#"Add Curve", each ([Budget Amount] <> 0)),
#"Changed Type" = Table.TransformColumnTypes(#"Filtered Rows",{{"Code", type text}, {"Cost Type", type text}, {"Manual Calculation", type text}, {"Unit Qty", type text}, {"Unit of Measure", type text}, {"Unit Cost", type text}, {"Budget Amount", type text}, {"Start Date", type text}, {"End Date", type text}, {"Curve", type text}}),
#"Reordered Columns" = Table.ReorderColumns(#"Changed Type",{"Code", "Cost Type", "Description", "Manual Calculation", "Unit Qty", "Unit of Measure", "Unit Cost", "Budget Amount", "Start Date", "End Date", "Curve"})
in
#"Reordered Columns"

[BA_Pete]

Hi @RVGypsy ,

Paste this code over the default code in Advanced Editor to follow the steps to do this.

I've left the steps separate to see each stage, but you could probably combine these all into one or two steps if you wanted:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyMDRTitUBswxMoSxTM2MTGNPSwlwpNhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [number = _t]),
    chgTypes = Table.TransformColumnTypes(Source,{{"number", Int64.Type}}),
    padStart = Table.AddColumn(chgTypes, "pad", each Text.PadStart(Text.From([number]), 6, "0")),
    splitGroups = Table.SplitColumn(padStart, "pad", Splitter.SplitTextByPositions({0, 2, 4}), {"pad.1", "pad.2", "pad.3"}),
    combineGroups = Table.AddColumn(splitGroups, "text", each Text.Combine({[pad.1],[pad.2],[pad.3]}, "-"))
in
    combineGroups

Pete

[RVGypsy]

It just gives me repating values instead of matching the value in the first column and transforming it into the text I need.InputOutput

[BA_Pete · Accepted solution]

Ok. You've put the code I provided into a custom column, rather than as a new query with Advanced Editor. The code I provided was just an example of how to perform the steps on your query.

To apply this to your query, you would use this code in a new custom column:

Text.Combine(
    Splitter.SplitTextByPositions({0, 2, 4})
    ( Text.PadStart(Text.From([Code]), 6, "0") ),
    "-"
)

Full example query to paste into Advanced Editor:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQ0NTZQitUBsowMDM1gLANTKMvUzNgExrS0MAczLYHAEKgpFgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Code = _t]),
    chgTypes = Table.TransformColumnTypes(Source,{{"Code", Int64.Type}}),
    addCodeText =
    Table.AddColumn(
        chgTypes,
        "codeText",
        each Text.Combine(
            Splitter.SplitTextByPositions({0, 2, 4})
            ( Text.PadStart(Text.From([Code]), 6, "0") ),
            "-"
        )
    )
in
    addCodeText

Output:

Pete

[RVGypsy]

Ok that is where i get lost as i dont know how to write or what words to use. If it wasnt for power query asking the questions I would be lost. I will study what you have here and see if I can learn this.

[RVGypsy]

Don't know exactly what I did bu I got this code to work in the column I needed it in and my sheet now wrks as I intended it to work.