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.
All,
In Power Query, How do I replace all non blanks with 1
I have tried the Excel Find and Replace method of * to 1
However this does not seem to work in Power BI
Solved! Go to Solution.
Hi @hdhillon , try this:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSi/KLy1QMFTSUQJhUwOlWB10QUMDrKLmpmiiRljVGuMUNUIRNYKai2KsEZoTgEwQAjETi1PSYNxYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Group = _t, Month = _t, Value = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Value", Int64.Type}, {"Month", Int64.Type}}),
#"Replaced Value" =
Table.ReplaceValue(
#"Changed Type",
each [Group],
each
let
varReplacementValue = if Text.Length([Group]) > 0 then "1" else [Group]
in
varReplacementValue,
Replacer.ReplaceText,
{"Group"}
)
in
#"Replaced Value"
It turns this:
Into this:
It looks for any field that has a length greater than 0 and replaces that field with "1" if it does.
How to use M code provided in a blank query:
1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done
5) See this article if you need help using this M code in your model.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingPowerquery
= IFERROR(IF(LEN(related(column_name))>0,"Y","-"),"-")
I know you said replace, but you could easily add a column like this:
Table.AddColumn(PriorStep, "NewColumnName", each if [Column] <> null then 1 else [Column], type number)
@hdhillon click the button beside the Edit Bar and input this formula
=Table.ReplaceValue(LastStepName,null,1,(x,y.z)=>if x=y or x="" then x else z,Table.ColumnNames(LastStepName))
LastStepName, is the last step name in your original M code
Hi @hdhillon , try this:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSi/KLy1QMFTSUQJhUwOlWB10QUMDrKLmpmiiRljVGuMUNUIRNYKai2KsEZoTgEwQAjETi1PSYNxYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Group = _t, Month = _t, Value = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Value", Int64.Type}, {"Month", Int64.Type}}),
#"Replaced Value" =
Table.ReplaceValue(
#"Changed Type",
each [Group],
each
let
varReplacementValue = if Text.Length([Group]) > 0 then "1" else [Group]
in
varReplacementValue,
Replacer.ReplaceText,
{"Group"}
)
in
#"Replaced Value"
It turns this:
Into this:
It looks for any field that has a length greater than 0 and replaces that field with "1" if it does.
How to use M code provided in a blank query:
1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done
5) See this article if you need help using this M code in your model.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingCovering 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.