Forum Discussion
Fill Down/Up per ID
Hi Anonymous ,
You can try something like this. Please open a blank query--> Advanced editor-->Remove any existing code and copy and paste the below code.let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcvTxUdJRMrLQNzTUNzIwMgJyDA3N9CzMlGJ1opWcHH0xZPUsIXKOoS6YcqZQOYip5khyeaU5OchmYpWDmokpFwsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Currency = _t, Date = _t, Value = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Currency", type text}, {"Date", type date}, {"Value", type number}}), #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Changed Type", {{"Date", type text}}, "en-GB"), List.Distinct(Table.TransformColumnTypes(#"Changed Type", {{"Date", type text}}, "en-GB")[Date]), "Date", "Value"), #"Demoted Headers" = Table.DemoteHeaders(#"Pivoted Column"), #"Transposed Table" = Table.Transpose(#"Demoted Headers"), #"Filled Down" = Table.FillDown(#"Transposed Table",{"Column1", "Column2", "Column3", "Column4"}), #"Transposed Table1" = Table.Transpose(#"Filled Down"), #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table1", [PromoteAllScalars=true]), #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Currency", type text}, {"28/11/2022", type number}, {"27/11/2022", type number}}), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type1", {"Currency"}, "Attribute", "Value"), #"Renamed Columns" = Table.RenameColumns(#"Unpivoted Other Columns",{{"Attribute", "Date"}}) in #"Renamed Columns"Input
Output
Kind regards,
Rohit
Please mark this answer as the solution if it resolves your issue.
Appreciate your kudos!
rohit_singh Thanks for your answer
We have a time series of 3 years and 20+ currencies. This Code doesnt work for our values. What do we need to change in the code?
Hi Anonymous ,
I have tried to make the code more dynamic now. Should work for multiple currencies and multiple dates but you might have to tweak the code a bit. I'm working off sample data so I cannot say exactly what changes will be needed, but this is a good enough foundation to build on.
Something you might need to ensure is that the data is sorted in descending order by date (most recent to oldest) before you perform the pivot and transpose operations. This is to ensure that the most recent date is picked up for the fill down operation
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fdExCsMwDEDRu3gOTmQqNRkdCl3SDi6dTG4QuvX+DUiDkOWO5iPrgWoNedvCENI8AoxpSul8AFCcKexDDWt+NDUu3PL71jaUufuzO7eWZiNGuvCf5WXbApEmjky9qvj5HoeGuk2g/hxD/Vb6+wTqN3bSH6fbxOnPsdNvpb9PnH5jJzanB01Fc0PQVjTHB431J0WL5vyguWjvf9b9Bw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Currency = _t, Date = _t, Value = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Currency", type text}, {"Date", type date}, {"Value", type number}}),
#"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Changed Type", {{"Date", type text}}, "en-GB"), List.Distinct(Table.TransformColumnTypes(#"Changed Type", {{"Date", type text}}, "en-GB")[Date]), "Date", "Value"),
#"Demoted Headers" = Table.DemoteHeaders(#"Pivoted Column"),
#"Transposed Table" = Table.Transpose(#"Demoted Headers"),
#"Filled Down" = Table.FillDown(#"Transposed Table",Table.ColumnNames(#"Transposed Table")),
#"Transposed Table1" = Table.Transpose(#"Filled Down"),
#"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table1", [PromoteAllScalars=true]),
#"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Currency", type text}, {"28/11/2022", type number}, {"27/11/2022", type number}}),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type1", {"Currency"}, "Attribute", "Value"),
#"Renamed Columns" = Table.RenameColumns(#"Unpivoted Other Columns",{{"Attribute", "Date"}})
in
#"Renamed Columns"
Input
Output
Kind regards,
Rohit
Please mark this answer as the solution if it resolves your issue.
Appreciate your kudos!