Fill Down/Up per ID

[Juleeees]

Hi, 

we need to Fill Down/Up by ID (currency) because we have no values for weekends. 

We want to fill it down by Currency ID. This is an example, we have (20+) currencies. 

Thanks in advantage 

[AnkitBI]

Try Below.

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcszJUdJRMjTUN7LUNzIwMgJyjPQMDJRidaKVPP2C0OUM9UxMwXKhwS6YcsYQfQgzLRBylmZoZsLlMI1ESJmZo5loDpNCMw1VHGEUkngsAA==", 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}}),
Custom1 = Table.AddColumn(
#"Changed Type",
"Test",
each
if [Value] = null then
let
Currency = [Currency],
DateVal = [Date]
in
Table.LastN(
Table.SelectRows(
#"Changed Type",
each _[Currency] = Currency and _[Date] > DateVal and _[Value] <> null
),
1
)[Value]{0}
else
[Value]
)
in
Custom1

[Juleeees]

Thanks bro

[Juleeees]

@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?

[rohit_singh]

Hi @Juleeees ,

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!

[rohit_singh · Accepted solution]

Hi @Juleeees ,

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!