Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Fill Down/Up per ID

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 

Need Help
Tips and Tricks
  • rohit_singh's avatar
    rohit_singh
    3 years ago

    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!

     

     

     

5 Replies

  • rohit_singh's avatar
    rohit_singh
    Solution Sage
    3 years ago

    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!

     

     

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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's avatar
      rohit_singh
      Solution Sage
      3 years ago

      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!

  • AnkitBI's avatar
    AnkitBI
    Solution Sage
    3 years ago

    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