Forum Discussion
Delimiter
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 addCodeTextOutput:
Pete
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.
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"