Using If to assign value to calculated columns

[Anonymous]

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?

[v-yulgu-msft · Accepted solution]

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

[ChandeepChhabra]

@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]

[ChandeepChhabra]

@Anonymous Not quite sure if I have understood your question.

1. I can't see any text in the vendor/billables column
2. How do you wish to see the output 

[Anonymous]

S

WHen Entry Type is Filtered as Expense, there is text in the Vendors/Billables column

[ChandeepChhabra]

Can you share a sample of the expected output?

[Anonymous]

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