Forum Discussion

mlilkas's avatar
mlilkas
Frequent Visitor
10 years ago
Solved

Leading Zero

Hello,   I am trying to set up some store numbers in Power BI so we can view information by store specifically. When I enter the store numbers, PowerBI will list items by leading number instead of ...
Need Help
  • asocorro's avatar
    asocorro
    10 years ago

    If your source data has leading zeros, then you need to solve this when you import the data (assuming you are not connecting directly to the data source), by making sure you format the column as text in the query editor.  Otherwise, you will need to create a calculated column that pads the store IDs with the correct number of leading zeros.

asocorro's avatar
asocorro
Skilled Sharer
10 years ago

If your source data has leading zeros, then you need to solve this when you import the data (assuming you are not connecting directly to the data source), by making sure you format the column as text in the query editor.  Otherwise, you will need to create a calculated column that pads the store IDs with the correct number of leading zeros.

  • mlilkas's avatar
    mlilkas
    Frequent Visitor
    10 years ago

    Thanks Asocorro, I was able to pad the data. To add the leading numbers I used format(column,"000"). I did not have the "" before so it was not doing anything when I entered the zeros.

  • mlilkas's avatar
    mlilkas
    Frequent Visitor
    10 years ago

    Hmm, I am trying to pad the numbers but FORMAT doesnt seem to be working, and I cant find a DAX formula that works either. Text.Pad doesnt seem to exist in Power BI

    • asocorro's avatar
      asocorro
      Skilled Sharer
      10 years ago

      I meant here:

       

       

      • vlookup's avatar
        vlookup
        New Member
        6 years ago

        Just what the DR ordered.  Thanks!

  • Maryann's avatar
    Maryann
    Helper I
    7 years ago

    I tried this solution but it gave me an error. I believe my problem is that the column I want to retain the leading zeroes on is the result of Max(column_name) .   My table is grouped by booking_ID, getting  max(column), and trying to use the result to merge booking_ID with max(seq) as a key to another table.

    So my problem is without the leading zeroes, a one to one is turning into a many to many

    12864     13

    128641   3    are both coming out 1286413 creating many to many for the join.

    When I do a format( max(seq), '000') I get an error.

     

    Also changing the format to text doesn't add the leading zeroes.

    thanks

    • SJ's avatar
      SJ
      Helper I
      7 years ago

      Hi Maryann,

       

      Tried a similar piece and have no issues but not sure what you are doing wrong.

      The formula looks to be missing [] and the location it is pulling data from but not sure.

       

      What you could try to make them unique is an "&" statement.

       

      column 1 &" - "&max(Seq)

       

      column 1 = column containing 12864 and 128641

       

      This should work unless the error is in your: max(seq).

       

      Hope this makes sense.

      SJ

       

       

      • Maryann's avatar
        Maryann
        Helper I
        7 years ago

        Thanks SJ , This looks like a good fix .

         

  • cuiping's avatar
    cuiping
    Frequent Visitor
    7 years ago

    Otherwise, you will need to create a calculated column that pads the store IDs with the correct number of leading zeros.

    How exactly can we performe above? thank you