Data Layout

[gauravnarchal]

Hello

How can I change the layout of my data in the horizontal format?

The current layout of the data is as below.

unique_key	Header	Value
1015422	EMD Reason	Select
1015422	FF1	M713810
1015422	FF10	NA
1015422	FF13	NA
1015422	FF16	TEST 1
1015422	FF2	CP214392
1015422	FF20	OPEN
1015422	FF3	NA
1015422	FF33	DEDW3EE
1015422	FF45	LONDON
1015422	FF46	200
1015422	FF47	KMKMHK9
1015422	FF48	Select
1015422	FF75	Select
1015422	Traveller Guid	HUHJI-HUHU-NKN-2342
1015423	FF33	NA
1015423	FF46	NA
1015423	FF47	NA
1015423	Traveller Guid	NA

Required Layout

Unique Key

Reason

FF1

FF10

FF13

FF16

FF2

FF20

FF3

FF33

FF45

FF46

FF47

FF48

FF75

Traveller Guid

1015422

Select

M713810

NA

NA

TEST 1

CP214392

OPEN

NA

DEDW3EE

LONDON

200

KMKMHK9

Select

Select

HUHJI-HUHU-NKN-2342

1015423

TEST

NA

NA

NA

NA

[Anonymous]

Add a matrix to the report canvas and select "unique key" as row, "Header" as column and "Value" as value

[Ahmedx]

pls try this

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dZDLDoIwEEV/hbCGpC8ElkaKaG0hAnFBWBDtwoRogo/vt12BGbqZm94z6Z2ZrvMxwhEjxA98LjPvrIfX82EetR719e33wbIjz7GpMsY0wQgyZERtoU8d/sZIw+vGw4DZuqsIZjQlENqgsuIKEEcStX7GswvlHEAWGTmVKivhf8yOSBDclcVGhBSyECmEifuAceRizTR89Tjqydt/7jdjFG1xPISmtqESKiSULU9B583+Nqbz4Gt+vOaDaNvR/wA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [unique_key = _t, Header = _t, Value = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"unique_key", Int64.Type}, {"Header", type text}, {"Value", type text}}),
    #"Pivoted Column" = Table.Pivot(#"Changed Type", List.Distinct(#"Changed Type"[Header]), "Header", "Value")
in
    #"Pivoted Column"

[Ahmedx · Accepted solution]

to know how to do this watch my video

https://1drv.ms/v/s!AiUZ0Ws7G26RjHqLL9UiMeJZbQWb?e=OxWutM