Solved Dynamically Pad Columns

[Nick Burns]

Given a table with Column Names and Lengths, I'd like to pad the columns in another:


Attached is an example of data used above.
My actual data will have 30+ columns.

 

[AliGW]

Here's a dynamic 365 solution:

=LET(p,BYCOL(Data[#Headers],LAMBDA(c,VLOOKUP(c,Paddings,2,0))),v,BYCOL(p,LAMBDA(c,REPT(" ",c))),VSTACK(Data[#Headers],v&Data))

The array formatting could be done using conditional formatting easily enough.

 

[JoePublic]

If you load your Paddings table into two lists, PadColumns and PadLengths, then you can do this:

= Table.TransformColumns(Source, List.Transform(PadColumns, each {_, (a)=>Text.PadStart(a,PadLengths{List.PositionOf(PadColumns,_)}," ")}))

 

[Nick Burns]

If you load your Paddings table into two lists, PadColumns and PadLengths, then you can do this:

= Table.TransformColumns(Source, List.Transform(PadColumns, each {_, (a)=>Text.PadStart(a,PadLengths{List.PositionOf(PadColumns,_)}," ")}))

Thanks, that's just the "nudge" I needed to get my solution. I was able to make the Paddings into a list of lists and using your code came up with this:

= Table.TransformColumns(Source, List.Transform(ColumnPaddings, each {_{0}, (a)=>Text.PadStart(a,_{1}," "),type text}))

Also, there was one other criteria to my Paddings table - a Fill type. If it's a "0", it pads the beginning of the field with "0", otherwise pad the end with blanks

And the new M-code, though a bit lengthy, becomes this:

= Table.TransformColumns(Source, List.Transform(ColumnPaddings, each if _{2}="0" then {_{0}, (a)=>Text.PadStart(a,_{1},"0"),type text} else {_{0}, (a)=>Text.PadEnd(a,_{1}," "),type text}))

As I was writing this up, I put in the logic of which function to use in the Padding table and replaced the BLANK words with a space " ".

let
    Source = Excel.CurrentWorkbook(){[Name="Paddings"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column Name", type text}, {"Length", Int64.Type}, {"Fill", type text}}),
    #"Added Custom" = Table.AddColumn(Source, "Function", each if [Fill] = "0" then Text.PadStart else Text.PadEnd),
    #"Trimmed Text" = Table.TransformColumns(#"Added Custom",{{"Fill", each if _ = "BLANK" then " " else _, type text}}),
    #"Added Custom1" = Table.AddColumn(#"Trimmed Text", "Custom", each Record.ToList(_)),
    Custom = #"Added Custom1"[Custom]
in
    Custom

And now the M-Code for my data looks "cryptic" but now concise:

= Table.TransformColumns(Source, List.Transform(ColumnPaddings, each {_{0}, (a)=>_{3}(a,_{1},_{2}),type text}))

Thanks again, I just needed that little inspiration to get this going.

 

[AliGW]

You didn't even acknowledge my suggestion, Nick - please don't ignore offers of help, even if they aren't quite what you want. Courtesy is the order of the day here. Thanks.