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.
Hello,
I load data with in a column several text that can change over time. I need only the LE 01 until LE 18 from this:
What I am doing now is replacing text per text with a new text:
Then I delimite this column to extract the number, e.g. 13, from this:
Since it is a lot of work to do this text per text and the text can change in the future, is there a better option? E.g. prevent loading this data? So only 'LE 01' until 'LE 18' is loaded in the first place?
Solved! Go to Solution.
Hi @Anonymous,
I'd like to suggest you add a custom column with if statement to check if records which start with 'LE ', otherwise replace it as null.
Then you can use fill functions to 'fill up' text to replace null records.
Finally, you can add custom column with text.middle function to extract number from custom column 'replaced'.
Full query:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ZY5Ba4QwEIX/yuC5hplotHjbjSPIWgNRS0EksLClh9KFvfXfd9Qtuu0pk/e9eW/GMaosgUbKwDpIMadoehqjU/Jfq+o3LuGVfVe7VoH7+vwGe+XYdwtu0ATZyTHDLFCqnw0uOsl31sP58n69XUJlw6GsV6Qwf2DWec+2l/x7ZP53NUW1XtMw4G6CGCVNq9muAMgUxnzc9hwwE8PMgXRBKMqRK+cZDrZ/MC4XDtxtqt4mBTr5LRIvGa0LxI1DXLaDgF725bGu7YYX9tA2sDOtzfviJJqmHw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Type = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Type", type text}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Replaced", each if Text.Start([Type],3)="LE " then [Type] else null), #"Filled Up" = Table.FillUp(#"Added Custom",{"Replaced"}), #"Added Custom1" = Table.AddColumn(#"Filled Up", "Custom", each Text.Middle([Replaced],3,2)) in #"Added Custom1"
Regards,
Xiaoxin Sheng
Hi @Anonymous,
I'd like to suggest you add a custom column with if statement to check if records which start with 'LE ', otherwise replace it as null.
Then you can use fill functions to 'fill up' text to replace null records.
Finally, you can add custom column with text.middle function to extract number from custom column 'replaced'.
Full query:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ZY5Ba4QwEIX/yuC5hplotHjbjSPIWgNRS0EksLClh9KFvfXfd9Qtuu0pk/e9eW/GMaosgUbKwDpIMadoehqjU/Jfq+o3LuGVfVe7VoH7+vwGe+XYdwtu0ATZyTHDLFCqnw0uOsl31sP58n69XUJlw6GsV6Qwf2DWec+2l/x7ZP53NUW1XtMw4G6CGCVNq9muAMgUxnzc9hwwE8PMgXRBKMqRK+cZDrZ/MC4XDtxtqt4mBTr5LRIvGa0LxI1DXLaDgF725bGu7YYX9tA2sDOtzfviJJqmHw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Type = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Type", type text}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Replaced", each if Text.Start([Type],3)="LE " then [Type] else null), #"Filled Up" = Table.FillUp(#"Added Custom",{"Replaced"}), #"Added Custom1" = Table.AddColumn(#"Filled Up", "Custom", each Text.Middle([Replaced],3,2)) in #"Added Custom1"
Regards,
Xiaoxin Sheng
@v-shex-msft Thank you! This is indeed working for me. Great pragmatic solution. thank you again.
@Anonymous Could you please provide a sample data to replicate your issue, so that it will be helpful to provide an accurate solution.
Proud to be a PBI Community Champion
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 |
---|---|
115 | |
99 | |
86 | |
70 | |
62 |
User | Count |
---|---|
151 | |
120 | |
103 | |
87 | |
68 |