Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to 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
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
@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 Not quite sure if I have understood your question.
S
WHen Entry Type is Filtered as Expense, there is text in the Vendors/Billables column
Can you share a sample of the expected output?
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
97 | |
85 | |
67 | |
59 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |