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`m trying to add a new column based on a column that contains an employees contracted hours. Full time hours are 37 and i`m looking for the following:
If the employees works:
Any help or ideas would be much appreciated.
Thanks,
Brendan
Solved! Go to Solution.
Try this -
Add Column > Custom Column
= Table.AddColumn(#"Changed Type", "Custom", each if [hours] >= 33.4 then "0.9-1" else if [hours] >= 29.7 then "0.8-0.9" else if [hours] >= 26 then "0.7-0.8" else if [hours] >= 22.3 then "0.6-0.7" else if [hours] >= 18.6 then "0.5-0.6" else if [hours] >= 14.9 then "0.4-0.5" else if [hours] >= 11.2 then "0.3-0.4" else if [hours] <= 11.1 then "0-0.3" else null)
Then Expandout the custom Column
Try this -
Add Column > Custom Column
= Table.AddColumn(#"Changed Type", "Custom", each if [hours] >= 33.4 then "0.9-1" else if [hours] >= 29.7 then "0.8-0.9" else if [hours] >= 26 then "0.7-0.8" else if [hours] >= 22.3 then "0.6-0.7" else if [hours] >= 18.6 then "0.5-0.6" else if [hours] >= 14.9 then "0.4-0.5" else if [hours] >= 11.2 then "0.3-0.4" else if [hours] <= 11.1 then "0-0.3" else null)
Then Expandout the custom Column
Many thanks - thats done the trick!
Thanks,
Brendan
a differente way to prepare the translation table:
lt's call it query transl2
let
Source = let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dc9LCgMhFETRrTwcx4f/z1rEUQg4SLohJmT7KXVqzw+3qFKEYkWStGZN7d3p0x4H3c/n93VQb+evE4BUbEW9FQFmhnacrrAFdgs7zgMn9lfYAfuFEwdgY9hcYQ8cJoayA3sM7GSAjEuGec9k1HcyQqYlM0dIa5HeyQSZpwRxQ0akdzJLzUrU+gc=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
#"Split Column by Delimiter" = Table.SplitColumn(Source, "Column1", Splitter.SplitTextByDelimiter("shows", QuoteStyle.Csv), {"Column1.1", "to"}),
#"Added Custom" = Table.AddColumn(#"Split Column by Delimiter", "from", each let l= List.Transform(List.RemoveMatchingItems(Text.SplitAny([Column1.1],Text.Combine({"a".."z","- "})),{""}),Number.From) in {l{0}..l{1}})
in
#"Added Custom"
here the code to make the translation required:
let
Source = List.Transform(List.Random(300), each Number.RoundDown(_*37,1)),
#"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Column1", Int64.Type}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "fff", each transl[to]{List.PositionOf(transl[from],[Column1]*10,Occurrence.First, (x,y)=>List.Contains(x,y))})
in
#"Added Custom"
following this rules:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dc9LCgMhFETRrTwcx4f/z1rEUQg4SLohJmT7KXVqzw+3qFKEYkWStGZN7d3p0x4H3c/n93VQb+evE4BUbEW9FQFmhnacrrAFdgs7zgMn9lfYAfuFEwdgY9hcYQ8cJoayA3sM7GSAjEuGec9k1HcyQqYlM0dIa5HeyQSZpwRxQ0akdzJLzUrU+gc=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "dict", each clean(_[Column1],{"0".."9", " ","-"},"")),
#"Split Column by Positions" = Table.SplitColumn(#"Added Custom", "dict", Splitter.SplitTextByPositions({0, 13}), {"from", "to"}),
cl = Table.TransformColumns(#"Split Column by Positions",{{"from", each {Number.From(Text.Split(_,"-"){0})..Number.From(Text.Split(_,"-"){1})}}})
in
cl
who relies on this function clean:
let
fnCngNotStndCharsToDef =( str_in as text, stndChrs, defChar as text)=>
let
str_out=Text.Combine( List.Transform(Splitter.SplitTextByRepeatedLengths(1)(str_in), each Record.FieldOrDefault(Record.FromList(stndChrs,stndChrs),_ ,defChar) ))
in
str_out
in
fnCngNotStndCharsToDef
Hi @Bfaws
If you are looking to do this in DAX, you cna use SWITCH: https://dax.guide/switch/
If you prefer to do it in the query editor, check this out: https://www.thebiccountant.com/2018/02/15/generic-switch-function-query-editor-in-power-bi-and-power...
Please mark the question solved when done and consider giving kudos if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
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.