Complicated unpivot

[Anonymous]

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 1	Column 2
8.1 - Is an RMS Owner appointed in writing? : Answer	Improvement Required
8.1 - Is an RMS Owner appointed in writing? : Comments	No CS RMS owner in place
8.2 - Do internal inspections check general controls-eg: signage and access control ? : Answer	Improvement Required
8.2 - Do internal inspections check general controls-eg: signage and access control ? : Comments	CS - Example
8.3 - Have suitable and sufficient ‘Confined Space’ risk assessments  been completed, with appropriate control measures identified, implemented and managed as appropriate: Answer	Improvement Required
8.4 - Are all entry points signed ?: Answer	Standard Not Met
8.5 - Is suitable rescue equipment provided and available?: Answer	Key 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?: Answer	Improvement Required
8.6 - Entry into a confined space is subject to a permit to work (Contractors & employees) & permits to work are completed in full?: Comments	No CS permit in place on site
8.7 - Have  contractors entering CS been adequately vetted to ensure they are suitably qualified? : Answer	Standard Not Met
8.7 - Have  contractors entering CS been adequately vetted to ensure they are suitably qualified? : Comments	No vetting of contractors who enter CS

[BA_Pete · Accepted solution]

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]

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

[BA_Pete]

No problem at all.

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

Pete