Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Splitting Columns in excel file correctly using power query

hi,

newbie to PowerBi so hoping for some help

i have a basic excel file that has an ID column and a "reason" column. the reason column can have multiple enteries in each cell, e.g.:

IDReason
1Left;Notice;
2Stayed;
3Stayed;No Notice;
4Did not like role;Left; No Notice; Wouldnt recommend;
5Enjoys Role

 

i want to split the Reason column so that i can have a bar chart that is a simple count of the highest hitting individual reason.

i tried using the "split columns" delimiter in power query but its not giving me accurate results. e.g. its showing i have 22 values for "Left" but in my excel file i have 30+

 

if theres only one Value in the Reason cell, it can either have no semicolon or it can, but if theres more than 1 reason in a cell, it always has a semicolon after each reason.

 

am i missing sometthing obvious here?

thanks for any help!

Marko

General Comment
Need Help

5 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      thanks for the tip youre 100% right! 🙂 

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      ah didnt see the advanced options and i was spliting by column. once i changed it to rwos it works. thanks a million! 

  • goncalogeraldes's avatar
    goncalogeraldes
    Icon for Super User rankSuper User
    4 years ago

    Hello there Anonymous ! Have you tried doing something like the following:

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8nRR0lEKSk0szs9TitWJVjIEcn1S00qs/fJLMpNTrcGCRkDB4JLEytQUCN8YwffLV0BWaQKUcclMUcjLL1HIycxOVSjKz0m1BhuogFCqEJ5fmpOSV6JQlJqcn5ubmgc11xSo2zUvK7+yWCEIqE8pNhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"(blank)" = _t, #"(blank).1" = _t]),
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    Custom1 = Table.ReplaceValue(#"Promoted Headers",each [Reason],each Text.Split([Reason],";"),Replacer.ReplaceValue,{"Reason"}),
    #"Expanded Reason" = Table.ExpandListColumn(Custom1, "Reason"),
    #"Filtered Rows" = Table.SelectRows(#"Expanded Reason", each ([Reason] <> "")),
    #"Changed Type" = Table.TransformColumnTypes(#"Filtered Rows",{{"Reason", type text}, {"ID", Int64.Type}})
in
    #"Changed Type"

     

    Hope this answer solves your problem!
    If you need any additional help please @ me in your reply.
    If my reply provided you with a solution, please consider marking it as a solution ✔️ or giving it a kudoe 👍
    Thanks!

    You can also check out my LinkedIn!

    Best regards,
    Gonçalo Geraldes