Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Complicated unpivot

Hi all,

 

I know the main focus should be to sort the source of data but in this case it really will not be possible.

 

So, I have a dataset which has as weird row setup, I need to pull the data into columns but the only unique thing about each row is a small word of either : Answer, :Comment, : Score and few others. I want to pull these into new columns called Answer, Score, Comment etc but then delete all the duplicate rows so I am left with one row.

 

Example of the data is pasted below, any ideas how I could achieve this?

 

Column 1Column 2
8.1 - Is an RMS Owner appointed in writing? : AnswerImprovement Required
8.1 - Is an RMS Owner appointed in writing? : CommentsNo CS RMS owner in place
8.2 - Do internal inspections check general controls-eg: signage and access control ? : AnswerImprovement Required
8.2 - Do internal inspections check general controls-eg: signage and access control ? : CommentsCS - Example
8.3 - Have suitable and sufficient ‘Confined Space’ risk assessments  been completed, with appropriate control measures identified, implemented and managed as appropriate: AnswerImprovement Required
8.4 - Are all entry points signed ?: AnswerStandard Not Met
8.5 - Is suitable rescue equipment provided and available?: AnswerKey Standard Met
8.6 - Entry into a confined space is subject to a permit to work (Contractors & employees) & permits to work are completed in full?: AnswerImprovement Required
8.6 - Entry into a confined space is subject to a permit to work (Contractors & employees) & permits to work are completed in full?: CommentsNo CS permit in place on site
8.7 - Have  contractors entering CS been adequately vetted to ensure they are suitably qualified? : AnswerStandard Not Met
8.7 - Have  contractors entering CS been adequately vetted to ensure they are suitably qualified? : CommentsNo vetting of contractors who enter CS
Need Help
  • BA_Pete's avatar
    BA_Pete
    3 years ago

    Hi Anonymous ,

     

    You can just split off the category by using the ": " delimiter, then pivot this column:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("zVTBbtNAEP2VkU8gJagUKKiXqApIVKhFao5tDht7nAxZ75qddYxv/Qz4vX4JM+s4TYQqpYdK3FbemffevHnr29vs05u3MIZLBuPg5moG31uHAUxde3IRCyAHbaBIbjmBc7hw3GLIRtllVQe/wQpdhBv82VDAIpuPnos39ZVCsCBee5jOUotPLVJYW5PjFvVUUD97UJDgjJUD15hH8o4hX2G+hiVKm9zk3sXgLY9xeQ5MS2eWKGoKMHmOzMM9HDvPyzDvTS5jj+HLL1PVdpj2nXz5ajYI3FA0C9vDcFOWlJNqfLj/PfWuJCeWzmqx6eH+DwTiNRhm4UrYAAtEJ7SKLOaPoKW40mUEXwcyEXeSKjTcBGSgQjqpJK0m7VMkIVH+yuhEcuZ9jONcfC8TXQSZw1qQ69BBSgQnnwRzsgczi8JmQgHXPsIVxi3Ehz5YO0tEbt4gKE+dKJVc9PdizcaQ1bp95G/YwQ79EflMF5BEiSQPRm3pvWX1FkhZFz9k65CuawwVpXPrwxpeTdVFk0cfGO6ak5PTM0DxzneI/Hr40jfxrssEfFyNxr1srJ0c5+Z/JfifR7xlG14weCdbjkO2Pw7Z7sO3VaEpC/JX0P6UWlPIyBIv28EGozKKDnSaUogr2aPK2WahAym1KbUHr/qJIL28gkNLtFuBfXlA2K58TyqM2Xz+Fw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Column 1" = _t, #"Column 2" = _t]),
    splitByDelim = Table.SplitColumn(Source, "Column 1", Splitter.SplitTextByEachDelimiter({": "}, QuoteStyle.Csv, true), {"Column 1.1", "Column 1.2"}),
    pivotCol = Table.Pivot(splitByDelim, List.Distinct(splitByDelim[#"Column 1.2"]), "Column 1.2", "Column 2")
in
    pivotCol

     

    Output:

     

    Pete

3 Replies

  • BA_Pete's avatar
    BA_Pete
    Super User
    3 years ago

    Hi Anonymous ,

     

    You can just split off the category by using the ": " delimiter, then pivot this column:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("zVTBbtNAEP2VkU8gJagUKKiXqApIVKhFao5tDht7nAxZ75qddYxv/Qz4vX4JM+s4TYQqpYdK3FbemffevHnr29vs05u3MIZLBuPg5moG31uHAUxde3IRCyAHbaBIbjmBc7hw3GLIRtllVQe/wQpdhBv82VDAIpuPnos39ZVCsCBee5jOUotPLVJYW5PjFvVUUD97UJDgjJUD15hH8o4hX2G+hiVKm9zk3sXgLY9xeQ5MS2eWKGoKMHmOzMM9HDvPyzDvTS5jj+HLL1PVdpj2nXz5ajYI3FA0C9vDcFOWlJNqfLj/PfWuJCeWzmqx6eH+DwTiNRhm4UrYAAtEJ7SKLOaPoKW40mUEXwcyEXeSKjTcBGSgQjqpJK0m7VMkIVH+yuhEcuZ9jONcfC8TXQSZw1qQ69BBSgQnnwRzsgczi8JmQgHXPsIVxi3Ehz5YO0tEbt4gKE+dKJVc9PdizcaQ1bp95G/YwQ79EflMF5BEiSQPRm3pvWX1FkhZFz9k65CuawwVpXPrwxpeTdVFk0cfGO6ak5PTM0DxzneI/Hr40jfxrssEfFyNxr1srJ0c5+Z/JfifR7xlG14weCdbjkO2Pw7Z7sO3VaEpC/JX0P6UWlPIyBIv28EGozKKDnSaUogr2aPK2WahAym1KbUHr/qJIL28gkNLtFuBfXlA2K58TyqM2Xz+Fw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Column 1" = _t, #"Column 2" = _t]),
    splitByDelim = Table.SplitColumn(Source, "Column 1", Splitter.SplitTextByEachDelimiter({": "}, QuoteStyle.Csv, true), {"Column 1.1", "Column 1.2"}),
    pivotCol = Table.Pivot(splitByDelim, List.Distinct(splitByDelim[#"Column 1.2"]), "Column 1.2", "Column 2")
in
    pivotCol

     

    Output:

     

    Pete

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Awesome thank you. It is crazy the sort of things that can be done in PQ to sort out messy source data!

      • BA_Pete's avatar
        BA_Pete
        Super User
        3 years ago

         

        No problem at all.

        But if you think your source data is messy, please never use the Power Query PDF connector! 😂

         

        Pete