Transposing data into a more readable format

[Malsk1_1]

Hi,

I need some help converting the raw data in the attached to a more readible format.  The raw data and he expected results are in the attachment.

Raw Data:

Fund Name

Sub Fund Name

ISIN

Launch date

Lifecycle

AE Status

AE

AR Status

AR

AT Status

AT

BE Status

BE

ABC

XYZ

LU0000000111

30/06/2011

Launched

De-Registered

28/02/2020

Registered

01/01/2018

Fiscal Certification only

01/01/2015

Expected Outcome:

Fund Name	Sub Fund Name	ISIN	Launch date	Lifecycle	ISO Country	Registration Status	Registration Date
ABC	XYZ	LU0000000111	30/06/2011	Launched	AE	De-Registered	28/02/2020
ABC	XYZ	LU0000000111	30/06/2011	Launched	AR	Registered	01/01/2018
ABC	XYZ	LU0000000111	30/06/2011	Launched	AT
ABC	XYZ	LU0000000111	30/06/2011	Launched	BE	Fiscal Certification only	01/01/2015

Fund NameSub Fund NameISINClassCurrencyLaunch dateLifecycleAE StatusAEAR StatusARAT StatusATBE StatusBE

I have tried the unpivot on the country columns but i am not able to get it into the format that i require. 

Any help would be appreciated.

Thanks in advance!

[Malsk1_1]

@Ashish_Mathur  thank you - this worked i was able to follow the M Code and managed to get the desired output!!

[Ashish_Mathur]

You are welcome.

[Ashish_Mathur · Accepted solution]

Hi,

This M code works

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnRyVtJRioiMApI+oQYQYGhoCOR6ArFraBCQNDbQNTDTNTIAC/skluYlZ6SmAJkuqbpBqemZxSWpRWC+kYWugRFQnZEBkIMiY2CoC0RAEyyAHAhyyyxOTsxRcE4tKslMy0xOLMnMz1PIz8upRFZuqhQbCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Fund Name" = _t, #"Sub Fund Name" = _t, ISIN = _t, Class = _t, Currency = _t, #"Launch date" = _t, Lifecycle = _t, #"AE Status" = _t, AE = _t, #"AR Status" = _t, AR = _t, #"AT Status" = _t, AT = _t, #"BE Status" = _t, BE = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Fund Name", type text}, {"Sub Fund Name", type text}, {"ISIN", type text}, {"Class", type text}, {"Currency", type text}, {"Launch date", type date}, {"Lifecycle", type text}, {"AE Status", type text}, {"AE", type date}, {"AR Status", type text}, {"AR", type date}, {"AT Status", type text}, {"AT", type text}, {"BE Status", type text}, {"BE", type date}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Fund Name", "Sub Fund Name", "ISIN", "Class", "Currency", "Launch date", "Lifecycle"}, "Attribute", "Value"),
    #"Added Custom" = Table.AddColumn(#"Unpivoted Other Columns", "ISO Country", each if Text.End([Attribute],6) = "Status" then Text.Start([Attribute],2) else null),
    #"Filled Down" = Table.FillDown(#"Added Custom",{"ISO Country"}),
    #"Added Custom1" = Table.AddColumn(#"Filled Down", "Registration Status", each if Text.End([Attribute],6) = "Status" then [Value] else null),
    #"Filled Down1" = Table.FillDown(#"Added Custom1",{"Registration Status"}),
    #"Added Custom2" = Table.AddColumn(#"Filled Down1", "Registration Date", each if Text.End([Attribute],6) <> "Status" then [Value] else null),
    #"Filled Up" = Table.FillUp(#"Added Custom2",{"Registration Date"}),
    #"Removed Columns" = Table.RemoveColumns(#"Filled Up",{"Attribute", "Value"}),
    #"Removed Duplicates" = Table.Distinct(#"Removed Columns")
in
    #"Removed Duplicates"

Hope this helps.

[Malsk1_1]

@Ashish_Mathur are you able to send me the pbix? that way i can also follow the steps you have done in query editor.

In reality i have more columns than i had in the example above.. so i will need to tweak the code for the additional columns

[Malsk1_1]

Data now attached.

Transpose Data Example