Pivot Table and Display the orginal Values instead of count/sum/etc

[Anonymous]

hi there, after pivoting the table, i lose all the values/texts.

Custom.1	Comment.Column2
Channel	SPOTV
Date/Time Interruption	01/Feb/25 0258 UTC (01/Feb/25 1058 SGT)
Date/Time Restoration	01/Feb/25 0340 UTC (01/Feb/25 1140 SGT)
Impacted Location	Headend
Effect of Incident	No Audio on both main and backup feeds
Channel	Zee Thirai / WION / Zee Tamil
Date/Time Interruption	04/Jan/25 1306 UTC (04/Jan/25 2106 SGT)
Date/Time Restoration	04/Jan/25 1400 UTC (04/Jan/25 2200 SGT)
Impacted Location	Headend
Effect of Incident	Loss of feed on main feeds
Channel	Times
Date/Time Interruption	09/Feb/25 1850 UTC (10/Feb/25 0250 SGT)
Date/Time Restoration	09/Feb/25 1905 UTC (10/Feb/25 0305 SGT)
Impacted Location	Headend
Effect of Incident	Loss of signal on backup feed
Channel	Drama
Date/Time Interruption	13/FEB/25 0927 UTC (13/FEB/25 1727 SGT)
Date/Time Restoration	13/FEB/25 0941 UTC (13/FEB/25 1741 SGT)
Impacted Location	Headend
Effect of Incident	Loss of signal on backup feeds

Because after pivoting, it is showing me this: 

Channel	Date/Time Interruption	Date/Time Restoration	Impacted Location	Effect of Incident
414	414	414	380	412

i want to maintain all the values as it is. and then clean it up somemore. thanks. appreciate any help. thanks.

Channel	Date/Time Interruption	Date/Time Restoration	Impacted Location	Effect of Incident
SPOTV	01/Feb/25 0258 UTC (01/Feb/25 1058 SGT)	01/Feb/25 0340 UTC (01/Feb/25 1140 SGT)	Headend	No Audio on both main and backup feeds

[Ritaf1983 · Accepted solution]

Hi again 🙂
You can apply the M code:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("tZJfT8IwFMW/ys2eNCGs3R+BRx2gGAJGpiYSHsp2J41rS7bu+9sN3AgzzkR9WvK7dyfnnNv12gqKXCvRp1bPCpQQKHU/UGkhpGNtema8Y1Jiaqarh2X4XLEx02iHXCDMpMYsK/aaK2lWCLWnuLUdH4jjD+EpDOCiYZQYtroNL89EHtE4yFhbw/VIS4MaVmvMxJ5FGmOYq+jz/ztkMcq4mk+SBCMNKjFGI26wNgsLBddFzBUoCVuldyAYl8BkDFsWvRd7SBDj/Cz8KyKEO54xDja8zJYL86kYEzztKMWz75mszLvk6hioZg41rLuURsMjpKXhkN+WMld5XqIye1lM1clXRZTm8o7Ao/paQ/9olpKTl0F+ELjRGBG/peEa9jeBc/4mWVq9heb6Z5nHGRPs+8zUtaeTm8rbyBkc/daMDgzrzHyq4dG2hmH/mPlwVDBr1mbzAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"(blank)" = _t, #"(blank).1" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"(blank)", type text}, {"(blank).1", type text}}),
#"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([#"(blank).1"] <> "")),
#"Promoted Headers" = Table.PromoteHeaders(#"Filtered Rows", [PromoteAllScalars=true]),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Promoted Headers", {"Custom.1"}, "Attribute", "Value"),
#"Added Index" = Table.AddIndexColumn(#"Unpivoted Other Columns", "Index", 0, 1, Int64.Type),
#"Inserted Integer-Division" = Table.AddColumn(#"Added Index", "Integer-Division", each Number.IntegerDivide([Index], 5), Int64.Type),
#"Removed Columns" = Table.RemoveColumns(#"Inserted Integer-Division",{"Index"}),
#"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[Attribute]), "Attribute", "Value"),
#"Pivoted Column1" = Table.Pivot(#"Pivoted Column", List.Distinct(#"Pivoted Column"[Custom.1]), "Custom.1", "Comment.Column2"),
#"Removed Columns1" = Table.RemoveColumns(#"Pivoted Column1",{"Integer-Division"})
in
#"Removed Columns1"
Ot follow my dteps from UI of PQ

This is a little more complexed then simple pivot column

pbix is attached

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly

[Ritaf1983]

Hi @Anonymous 
Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...
Please show the expected outcome based on the sample data you provided.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

[Anonymous]

apologies, posted sample tables. thanks.