Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

Using If to assign value to calculated columns

Hey  guys, am very new to Power BI and  I am working on a data set in which one of the columns is  called 'vendors/Billables'.  This column has text/ numbers in it. I would want to create a new column called billable and assign only the numeric values of  'vendors/Billables' to the respective row of the newly created column, seems pretty complicated. Can someone help me with this?

Need Help
  • v-yulgu-msft's avatar
    v-yulgu-msft
    8 years ago

    Hi Anonymous,

     

    Please set the data type of column [vendors/Billables] to text in source table before loading to desktop.

     

    Then, add a custom column with below M code. (Query Editor mode -> Advanced Editor)

    let
    CharsToRemove = List.Transform({33..45,47,58..126}, each Character.FromNumber(_)),
    Source = Excel.Workbook(File.Contents("C:\Users\yulianag\Desktop\Sample Data.xlsx"), null, true),
    Table_1_Sheet = Source{[Item="Table_1",Kind="Sheet"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(Table_1_Sheet,{{"Column1", type text}, {"Column2", type text}}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Entry Type", type text}, {"Vendors/Billables", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type1", "Result", each Text.Remove([#"Vendors/Billables"],CharsToRemove))
  in
    #"Added Custom"

     

    Reference:

    Keep Only Numbers in Power Query

     

    Best regards,

    Yuliana Gu

7 Replies

  • ChandeepChhabra's avatar
    ChandeepChhabra
    Impactful Individual
    8 years ago

    Anonymous, Does a single row of the 'vendors/Billables' column contain both numbers and text? Can you share the snapshot of your data and the result that you expect ?

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      • Anonymous's avatar
        Anonymous
        Not applicable
        8 years ago

        Hi,

         

        So basically the Entry type column has several values. Currently it is filtered on Revenue(In the pic). The Vendors/ Billables column has numbers only on certain rows when 'Entry type' is filtered as 'Revenue' . THe vendors/ billables column has texts for other rows(ie, when ENtry type is filterd differently).

         

        THe output that I am expecting is as follows:

        I want to create another column called billables and assign only the numbers present in the Vendors/ Billables column to it. 

         

        This is my first time with Power BI.  Please help me

  • v-yulgu-msft's avatar
    v-yulgu-msft
    Microsoft Employee
    8 years ago

    Hi Anonymous,

     

    Please set the data type of column [vendors/Billables] to text in source table before loading to desktop.

     

    Then, add a custom column with below M code. (Query Editor mode -> Advanced Editor)

    let
    CharsToRemove = List.Transform({33..45,47,58..126}, each Character.FromNumber(_)),
    Source = Excel.Workbook(File.Contents("C:\Users\yulianag\Desktop\Sample Data.xlsx"), null, true),
    Table_1_Sheet = Source{[Item="Table_1",Kind="Sheet"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(Table_1_Sheet,{{"Column1", type text}, {"Column2", type text}}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Entry Type", type text}, {"Vendors/Billables", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type1", "Result", each Text.Remove([#"Vendors/Billables"],CharsToRemove))
  in
    #"Added Custom"

     

    Reference:

    Keep Only Numbers in Power Query

     

    Best regards,

    Yuliana Gu