Power Query Modeling

[Anonymous]

Hi , i have a requirement to complete in Power Query.

Given scenario is this ,

Output Required is this : 

Period      |  Exchange rate(euro)   |  Exchange rate(usd)  |

4/1/2019            77.05                             68.7

4/1/2018            80.1                               65

and the other values are continued below.              Period , exchange rates should be the column headers.

Is this possible in Power Query? I can't use DAX as i still need to model the data further , any help would be appreciated.

[AlB]

Hi @Anonymous 

Can you paste the sample of your data in text-taular format here (instead of screen capture) so that it can be copied easily and worked on?

Please mark the question solved when done and consider giving kudos if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[Anonymous]

Column3Column5

Period	4/1/2019
Exchange rate (Euro)	77.05
Exchange rate (USD)	68.7
Period	4/1/2018
Exchange rate (Euro)	80.1
Exchange rate (USD)	65
Period	4/1/2020
Exchange rate (Euro)	81.95
Exchange rate (USD)	75.95
Period	8/1/2018
Exchange rate (Euro)	78.4
Exchange rate (USD)	69.4
Period	8/1/2019
Exchange rate (Euro)	77.8
Exchange rate (USD)	70.15
Period	12/1/2018
Exchange rate (Euro)	78.95
Exchange rate (USD)	70.05
Period	12/1/2019
Exchange rate (Euro)	77.85
Exchange rate (USD)	70.7
Period	2/1/2018
Exchange rate (Euro)	78.3
Exchange rate (USD)	63.15
Period	2/1/2019
Exchange rate (Euro)	79.25
Exchange rate (USD)	70.3
Period	2/1/2020
Exchange rate (Euro)	77.85
Exchange rate (USD)	70.95
Period	1/1/2018
Exchange rate (Euro)	76.5
Exchange rate (USD)	63.1
Period	1/1/2019
Exchange rate (Euro)	79.7
Exchange rate (USD)	70.4
Period	1/1/2020
Exchange rate (Euro)	78.45
Exchange rate (USD)	70.45
Period	7/1/2018
Exchange rate (Euro)	78.6
Exchange rate (USD)	67.9
Period	7/1/2019
Exchange rate (Euro)	75.95
Exchange rate (USD)	67.95
Period	6/1/2018
Exchange rate (Euro)	77.35
Exchange rate (USD)	67.4
Period	6/1/2019
Exchange rate (Euro)	76.9
Exchange rate (USD)	68.7
Period	3/1/2019
Exchange rate (Euro)	77.05
Exchange rate (USD)	68.07
Period	3/1/2018
Exchange rate (Euro)	79.04
Exchange rate (USD)	63.78
Period	3/1/2020
Exchange rate (Euro)	81.28
Exchange rate (USD)	73.94
Period	5/1/2018
Exchange rate (Euro)	78.85
Exchange rate (USD)	66.95
Period	5/1/2019
Exchange rate (Euro)	77.35
Exchange rate (USD)	69.5
Period	11/1/2018
Exchange rate (Euro)	80.15
Exchange rate (USD)	71.25
Period	11/1/2019
Exchange rate (Euro)	77.3
Exchange rate (USD)	70.3
Period	10/1/2019
Exchange rate (Euro)	77.65
Exchange rate (USD)	70.6
Period	10/1/2018
Exchange rate (Euro)	83.3
Exchange rate (USD)	72.6
Period	9/1/2019
Exchange rate (Euro)	77.85
Exchange rate (USD)	71.1
Period	9/1/2018
Exchange rate (Euro)	83.45
Exchange rate (USD)	71.95

@AlB , will this help?

[AlB · Accepted solution]

Hi @Anonymous 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("rZZLS8NQEIX/SrkrhTK9j9zX3u4FcRW6KBrUTQtBwZ9vqk0Xk+m5I3Q94evhy8xp+t48DuPH8dWsTbdxG29dNbt1b7bfL+/7w9uwGvefw+pu+zUe76dnciYbpQeenx5O81Qo/445tSBqseQQNEpIbyHSUUVBc5znF2xRJM2FOpS0nscc2pIq/ugcdbLDojqvy4oV2PldLrjNuA0u2wFl2oDMhoUEVdZKvpE1SFS8XAoDfLmcRkEieFvhfCUc2jKQcdROgjYETGfQENAxAVm3AwkJyFRFKBYQ8RmcqCxr0mTNFBrYTqTisInE+dV6DbcpbStjsYFKFpZhoFwkbLO5PazDQJWZjbrdgjeb0mINos4tXoNKvAk0VVAunX9Fgpt7bcFtxv1XHTqro6ZGHSQZiyUEHNZzar3Fv5fjLVt1UXEh/n2O7H4A", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column3 = _t, Column5 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column3", type text}, {"Column5", type text}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Custom", each Number.IntegerDivide([Index],3)),
    #"Pivoted Column" = Table.Pivot(#"Added Custom", List.Distinct(#"Added Custom"[Column3]), "Column3", "Column5"),
    #"Grouped Rows" = Table.Group(#"Pivoted Column", {"Custom"}, {{"Period", each List.Max([Period]), type text}, {"Exchange rate (Euro)", each List.Max([#"Exchange rate (Euro)"]), type text}, {"Exchange rate (USD)", each List.Max([#"Exchange rate (USD)"]), type text}}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Grouped Rows",{{"Exchange rate (Euro)", type number}, {"Exchange rate (USD)", type number}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"Custom"})
in
    #"Removed Columns"

 Please mark the question solved when done and consider giving kudos if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers