How to separate the salary amount from the range in a different column

[fabiolamelo]

Hi everyone,

I have a table with a number of salaries and a range of it. I am trying to find a way to separate the salary amount from the range in different tables.

This is the table now

City                        IdCountry            Salary and Range             

 Canberra            ACT                        61          

 Canberra            ACT                        56 - 66  

 Sydney                 NSW                      56          

 Sydney                 NSW                      51 - 61  

 Regional              NSW                      48 - 65  

 Regional              NSW                      55          

 Darwin                 NT                          51          

 Darwin                 NT                          46 - 57  

I would like the table to return this

City                        IdCountry            Range of Salary       Salaries

Canberra             ACT                        56 - 66                      61

Sydney                 NSW                      51 - 61                      56

Regional               NSW                      48 - 65                      55

Darwin                  NT                          46 - 57                      51

Is there a way to solve this problem?

Many thanks in advance for your support

Fabi Melo

[parry2k · Accepted solution]

@fabiolamelo start a blank query and click advanced editor and paste this M code and see if that is what you need

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WOrTAOTEvKbWoKPHQAghU0lGCMSFQwdE5BE0ElYtFn4KZIVY1sTq0s9LUTEFXwcwMyZ7gypS81EoFHBqQtPoFh+O1Ck0f0Co8vqOdrYYgDxoiWRWUmp6Zn5eYg9cmgvagB6SJBcgeU5rbY2qKJxRdEovKM/OICcUQLIJ4ghEemANouQkoqZqaQ2yMBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [City = _t, IdCountry = _t, #"Salary and Range" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"City", type text}, {"IdCountry", type text}, {"Salary and Range", type text}}),
    #"Trimmed Text" = Table.TransformColumns(#"Changed Type",{{"City", Text.Trim, type text}, {"IdCountry", Text.Trim, type text}, {"Salary and Range", Text.Trim, type text}}),
    #"Added Custom" = Table.AddColumn(#"Trimmed Text", "Flag", each Text.PositionOf([Salary and Range],"-")),
    #"Filtered Range" = Table.SelectRows(#"Added Custom", each ([Flag] <> -1)),
    #"Filtered Salary" = Table.SelectRows(#"Added Custom", each ([Flag] = -1)),
    #"Merged Queries" = Table.NestedJoin(#"Filtered Salary", {"City", "IdCountry"}, #"Filtered Range", {"City", "IdCountry"}, "Salary Range", JoinKind.LeftOuter),
    #"Expanded Salary Range" = Table.ExpandTableColumn(#"Merged Queries", "Salary Range", {"Salary and Range"}, {"Salary and Range.1"}),
    #"Renamed Columns" = Table.RenameColumns(#"Expanded Salary Range",{{"Salary and Range", "Salary"}, {"Salary and Range.1", "Range"}}),
    #"Removed Columns" = Table.RemoveColumns(#"Renamed Columns",{"Flag"})
in
    #"Removed Columns"

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.⚡

[ryan_mayu]

@fabiolamelo 

Maybe you can try below meausre to create a new table

Table 2 = SUMMARIZE(Sheet2,Sheet2[City],Sheet2[IdCountry],"value",MAX(Sheet2[range]))

Hope this is helpful.

[amitchandak · Accepted solution]

@fabiolamelo , split the column by delimiter, Trim the space and then add two new columns and take Avg

https://www.tutorialgateway.org/how-to-split-columns-in-power-bi/

in M

=([col1]+[col2])/2

[parry2k · Accepted solution]

@fabiolamelo start a blank query and click advanced editor and paste this M code and see if that is what you need

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WOrTAOTEvKbWoKPHQAghU0lGCMSFQwdE5BE0ElYtFn4KZIVY1sTq0s9LUTEFXwcwMyZ7gypS81EoFHBqQtPoFh+O1Ck0f0Co8vqOdrYYgDxoiWRWUmp6Zn5eYg9cmgvagB6SJBcgeU5rbY2qKJxRdEovKM/OICcUQLIJ4ghEemANouQkoqZqaQ2yMBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [City = _t, IdCountry = _t, #"Salary and Range" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"City", type text}, {"IdCountry", type text}, {"Salary and Range", type text}}),
    #"Trimmed Text" = Table.TransformColumns(#"Changed Type",{{"City", Text.Trim, type text}, {"IdCountry", Text.Trim, type text}, {"Salary and Range", Text.Trim, type text}}),
    #"Added Custom" = Table.AddColumn(#"Trimmed Text", "Flag", each Text.PositionOf([Salary and Range],"-")),
    #"Filtered Range" = Table.SelectRows(#"Added Custom", each ([Flag] <> -1)),
    #"Filtered Salary" = Table.SelectRows(#"Added Custom", each ([Flag] = -1)),
    #"Merged Queries" = Table.NestedJoin(#"Filtered Salary", {"City", "IdCountry"}, #"Filtered Range", {"City", "IdCountry"}, "Salary Range", JoinKind.LeftOuter),
    #"Expanded Salary Range" = Table.ExpandTableColumn(#"Merged Queries", "Salary Range", {"Salary and Range"}, {"Salary and Range.1"}),
    #"Renamed Columns" = Table.RenameColumns(#"Expanded Salary Range",{{"Salary and Range", "Salary"}, {"Salary and Range.1", "Range"}}),
    #"Removed Columns" = Table.RemoveColumns(#"Renamed Columns",{"Flag"})
in
    #"Removed Columns"

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.⚡