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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Juleeees
Frequent Visitor

Fill Down/Up per ID

Hi, 

 

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

 

Juleeees_0-1669985973652.png

 

 

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

 

Thanks in advantage 

1 ACCEPTED SOLUTION
rohit_singh
Solution Sage
Solution Sage

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

rohit_singh_0-1669986506811.png

Output

rohit_singh_1-1669986525481.png

 

Kind regards,

Rohit


Please mark this answer as the solution if it resolves your issue.
Appreciate your kudos!

 

 

 

View solution in original post

5 REPLIES 5
AnkitBI
Solution Sage
Solution Sage

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
Frequent Visitor

Thanks bro

Juleeees
Frequent Visitor

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

rohit_singh_1-1669989259661.png

Output

rohit_singh_0-1669989233230.png

 

 

Kind regards,

Rohit


Please mark this answer as the solution if it resolves your issue.
Appreciate your kudos!

rohit_singh
Solution Sage
Solution Sage

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

rohit_singh_0-1669986506811.png

Output

rohit_singh_1-1669986525481.png

 

Kind regards,

Rohit


Please mark this answer as the solution if it resolves your issue.
Appreciate your kudos!

 

 

 

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors
Top Kudoed Authors