Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Trying to create a Column with below Logic
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Text.Combine( List.ReplaceMatchingItems(Text.ToList ([CommaSeparatedNumbersColumnName]), { {100,"A" },{"200","B" },{"300","C" },{",","." } })),type text)
expected result
100,200,300 ---> A.B.C
100------------->A
100,200 --------->A,B
Note : only {",","." } this part replaced
Solved! Go to Solution.
@PowerBITesting - You can use SUBSTITUTE in DAX:
New Column =
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE([Column],"100","A"),
"200","B"
),
"300","C"
)
Should be able to do the same thing in Power Query essentially nesting your replacer functions. @ImkeF @edhans
Try this @PowerBITesting
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQw0DECYmMDA6VYHTAfRkPEYgE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
#"Added Custom" =
Table.AddColumn(
Source,
"Custom",
each
Text.Combine(
List.ReplaceMatchingItems(
{Text.Split([Column1], ",")}{0},
{{"100","A"},{"200","B"},{"300","C"}}
),"."
)
)
in
#"Added Custom"
It generates this (NOTE: Above code properly replaces the comma with period. My screenshot below doesn't reflect that)
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 Reporting@PowerBITesting - You can use SUBSTITUTE in DAX:
New Column =
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE([Column],"100","A"),
"200","B"
),
"300","C"
)
Should be able to do the same thing in Power Query essentially nesting your replacer functions. @ImkeF @edhans
@PowerBITesting , if they are comma-separated number then use Text.Split in place of Text.ToList and try
@amitchandak - did you try your suggestion? It just returns an error when I try that simple replacement.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI Reporting@edhans , There was a formula give with the result and I assumed it working and text.Split will convert comma-separated text string and that is what suggested. Seem like that is needed too.
That is why I was asking @amitchandak . If I read @PowerBITesting 's original post correctly, they were getting the column with the red (1) below, and wanted what shows up in column marked with the red (2).
But I still cannot get your solution to replace Text.ToList with Text.Split. It returns an error, and doesn't do the proper replacement of 100 with A, 200, with B, etc.
But I could be wrong. That is why I was asking if you tested it as I'd like to see your solution to better understand. I have been unable to get it to work.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingUser | Count |
---|---|
101 | |
90 | |
79 | |
70 | |
70 |
User | Count |
---|---|
112 | |
96 | |
96 | |
75 | |
72 |