Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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.
Solved! Go to 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
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
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?
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