Fact table primary key as text or number

[PowerBITestingG]

So my primary key in my fact table is actually a number, but users want it to be padded with leading zeros. I understand that labeling that as text increases the size by a lot. Would it be better to make that field a number and then create a new column where its padded or is that redundant?

[v-shex-msft · Accepted solution]

Hi @PowerBITestingG,

In fact, this should relate to your table data amounts. Adding an additional column will affect the performance. You can simply use the 'Text.Length' and 'Text.Repeat' functions to add zero before the string.

Column =
        Table.AddColumn(
            #"Previous Step",
            "columnName",
            each
                Text.Repeat("0", 8 - Text.Length([ID]))
                & Text.From([ID])
        )

Text.Repeat - PowerQuery M | Microsoft Docs

Regards,

Xiaoxin Sheng

[jdbuchanan71]

@PowerBITestingG  That would be redundant and would increase the size more than just converting the primary field to text and adding the leading 0's.