Forum Discussion

Manikanadan-R's avatar
Manikanadan-R
New Member
4 years ago
Solved

How to replace values in a column in power query

I am trying to take only selected columns from my raw data for report which will be automatically updated when I add details in my raw data file.   The raw data file has so many columns including "...
Need Help
  • Vijay_A_Verma's avatar
    Vijay_A_Verma
    4 years ago

    You had used int64.type for Invoice No which won't work for alphanumeric data type. The same needs to be changed to text.

    Look below the step where I have changed it to text

    Table.TransformColumnTypes(#"Promoted Headers",{{"Branch", type text}, {"FY", type text}, {"Invoice No", type text}})
  • wdx223_Daniel's avatar
    wdx223_Daniel
    4 years ago

    try to delete the step of "Change Type"

Vijay_A_Verma's avatar
Vijay_A_Verma
Icon for Most Valuable Professional rankMost Valuable Professional
4 years ago

Try to clean the data first in those columns as there might be few invisible characters.

Transform menu - Format - Clean

  • Manikanadan-R's avatar
    Manikanadan-R
    New Member
    4 years ago

    Thanks for the suggestion Vijay. But it didn't work either. Still there's error.

     

    DataFormat.Error: We couldn't convert to Number. Details: 1-H

     

     

     

     

    • Vijay_A_Verma's avatar
      Vijay_A_Verma
      Icon for Most Valuable Professional rankMost Valuable Professional
      4 years ago

      Can you paste some data in a text file or Excel and share through Onedrive? 

      • Manikanadan-R's avatar
        Manikanadan-R
        New Member
        4 years ago

        https://1drv.ms/u/s!AuTuEhWYL9eEjyWPJ1quqFaxW1at?e=tPiIaz

         

        To make it simple I have just selected first 3 columns from my original data.

         

        There are 2 workbooks.

         

        1- Raw Data (The file where I save all my day to day activity)

         

        2- Power Query file (The file where I want the selected columns to be updated automatically as I keep updating my raw data)