Forum Discussion

Gadwood74's avatar
Gadwood74
Frequent Visitor
7 years ago
Solved

Addcolumn + format + &&

Hi - newby here.

I want to add a column in a table from 2 fields: Account and sub account.

 

The account is 123

Sub account is 45

 

My standard GL format is "000000-0000"

 

I'm looking for the correct formula. The way I think it (that obviously doesn't work) is:

 

#"KeyGL" = Table.AddColumn(DBIFGV_Table, "KeyGLBase", each FORMAT([DBGV_ACCOUNT],"000000") & FORMAT([DBGV_SUB_ACCOUNT],"0000"))

 

The result I'm looking for would be 00123-0045

Thanks!

Need Help
  • HotChilli's avatar
    HotChilli
    7 years ago
    = Table.AddColumn(Source, "Custom", each Text.PadStart([Column1], 6, "0") & "-" & Text.PadStart([Column2], 4,"0"))

    Make sure that the two fields are text before this line.  I think there's a slight mismatch between your format ( is "000000-0000") and the result you gave (00123-0045)

2 Replies

  • HotChilli's avatar
    HotChilli
    Community Champion
    7 years ago
    = Table.AddColumn(Source, "Custom", each Text.PadStart([Column1], 6, "0") & "-" & Text.PadStart([Column2], 4,"0"))

    Make sure that the two fields are text before this line.  I think there's a slight mismatch between your format ( is "000000-0000") and the result you gave (00123-0045)