Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Anonymous
Not applicable

Power Query Modeling

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

 

Given scenario is this ,

bi6.PNG

 

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.

1 ACCEPTED SOLUTION
AlB
Community Champion
Community Champion

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 

SU18_powerbi_badge

View solution in original post

3 REPLIES 3
AlB
Community Champion
Community Champion

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 

SU18_powerbi_badge

Anonymous
Not applicable

Column3Column5

Period4/1/2019
Exchange rate (Euro)77.05
Exchange rate (USD)68.7
Period4/1/2018
Exchange rate (Euro)80.1
Exchange rate (USD)65
Period4/1/2020
Exchange rate (Euro)81.95
Exchange rate (USD)75.95
Period8/1/2018
Exchange rate (Euro)78.4
Exchange rate (USD)69.4
Period8/1/2019
Exchange rate (Euro)77.8
Exchange rate (USD)70.15
Period12/1/2018
Exchange rate (Euro)78.95
Exchange rate (USD)70.05
Period12/1/2019
Exchange rate (Euro)77.85
Exchange rate (USD)70.7
Period2/1/2018
Exchange rate (Euro)78.3
Exchange rate (USD)63.15
Period2/1/2019
Exchange rate (Euro)79.25
Exchange rate (USD)70.3
Period2/1/2020
Exchange rate (Euro)77.85
Exchange rate (USD)70.95
Period1/1/2018
Exchange rate (Euro)76.5
Exchange rate (USD)63.1
Period1/1/2019
Exchange rate (Euro)79.7
Exchange rate (USD)70.4
Period1/1/2020
Exchange rate (Euro)78.45
Exchange rate (USD)70.45
Period7/1/2018
Exchange rate (Euro)78.6
Exchange rate (USD)67.9
Period7/1/2019
Exchange rate (Euro)75.95
Exchange rate (USD)67.95
Period6/1/2018
Exchange rate (Euro)77.35
Exchange rate (USD)67.4
Period6/1/2019
Exchange rate (Euro)76.9
Exchange rate (USD)68.7
Period3/1/2019
Exchange rate (Euro)77.05
Exchange rate (USD)68.07
Period3/1/2018
Exchange rate (Euro)79.04
Exchange rate (USD)63.78
Period3/1/2020
Exchange rate (Euro)81.28
Exchange rate (USD)73.94
Period5/1/2018
Exchange rate (Euro)78.85
Exchange rate (USD)66.95
Period5/1/2019
Exchange rate (Euro)77.35
Exchange rate (USD)69.5
Period11/1/2018
Exchange rate (Euro)80.15
Exchange rate (USD)71.25
Period11/1/2019
Exchange rate (Euro)77.3
Exchange rate (USD)70.3
Period10/1/2019
Exchange rate (Euro)77.65
Exchange rate (USD)70.6
Period10/1/2018
Exchange rate (Euro)83.3
Exchange rate (USD)72.6
Period9/1/2019
Exchange rate (Euro)77.85
Exchange rate (USD)71.1
Period9/1/2018
Exchange rate (Euro)83.45
Exchange rate (USD)71.95

 

@AlB , will this help?

AlB
Community Champion
Community Champion

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 

SU18_powerbi_badge

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.