Table Transformation

Hi SJHALANI ,

Please try:

First Transpose your data:

Then add an index column:

Add custom column:

Reorder and remove columns:

Transpose and remove column:

Transpose table and use first row as header:

Rename the column 1:

Here is the M code:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnR1UXAuLcpLrlTSUYIgRz93JKFYnWgll8SSVCA7oELBNzMFoghVCKTIyMDIWNdM19AMKGysZ2ZuZAlRiSxuqGduiaLYFKrYGFWtKTa1JjgMNsGm2BiHYmNsio1wKDaCK44FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t, Column5 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}}),
    #"Transposed Table" = Table.Transpose(#"Changed Type"),
    #"Added Index" = Table.AddIndexColumn(#"Transposed Table", "Index", 0, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Currency", each let 
id = _[Index]
in 
if [Column2] = "Px Mid" 
then
Table.SelectRows(#"Added Index", each [Index] = id-1)[Column1]{0}
else null),
    #"Reordered Columns" = Table.ReorderColumns(#"Added Custom",{"Currency", "Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Index"}),
    #"Removed Columns" = Table.RemoveColumns(#"Reordered Columns",{"Column1", "Column2", "Index"}),
    #"Transposed Table1" = Table.Transpose(#"Removed Columns"),
    #"Removed Columns1" = Table.RemoveColumns(#"Transposed Table1",{"Column3", "Column4"}),
    #"Transposed Table2" = Table.Transpose(#"Removed Columns1"),
    #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table2", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Column1", type text}, {"2023-6-16", type number}, {"2023-6-15", type number}, {"2023-6-14", type number}, {"2023-6-13", type number}, {"2023-6-12", type number}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type1",{{"Column1", "Currency"}})
in
    #"Renamed Columns"

Best Regards,

Jianbo Li

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