Forum Discussion
Solved
Transposing data into a more readable format
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!
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.
5 Replies
Data now attached.
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.
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
Ashish_Mathur thank you - this worked i was able to follow the M Code and managed to get the desired output!!
You are welcome.
