Table Transformation

[SJHALANI]

Hi Guys!

I have the below table in Power BI which has Last 5 days currency FX rates. 

I want to Transform the above table to the format given below.

Currency	6/16/2023	6/15/2023	6/15/2023	6/15/2023	6/15/2023
AED Currncy	3.6729	3.673	3.6729	3.6729	3.6729
ANG Currncy	1.79	1.79	1.79	1.79	1.79

I am looking for steps to be used in Power BI to do the above transformation. Any help is appreciated, TIA! 

[v-jianboli-msft]

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.

[SJHALANI]

Hi,
The above would be great if I had only 2 currency types. Problem is that I have around 100 currencies, so this will require me to create 100 such tables and append. 

[Kishore_KVN]

Hello @SJHALANI ,

Please follow below steps to achieve it:

1. You have to duplicate the table remove blank columns and other columns by keeping only two columns for each currency type in one table. 

2. Use first row as headers two time to get date and PX value at top, then create a custom column saying Currency Type". Then the table looks as below:

3. Now after creating tables like that append them to create third table. Output looks like this:

4. After loading data into Power BI desktop, create a Matrix visual which will creates report as you need. Please refer to below screenshot for the same:

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