Forum Discussion
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 ...
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 pivotColOutput:
Pete
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
Awesome thank you. It is crazy the sort of things that can be done in PQ to sort out messy source data!
No problem at all.
But if you think your source data is messy, please never use the Power Query PDF connector! 😂
Pete