The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredCompete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.
I have a list of dates for each month
for example
Date Value
01/Jan/2023 15
12/Jan/2023 25
25/Jan/2023 37
01/Feb/2023 48
15/Feb/2023 51
28/Feb/2023 57
06/Mar/2023 64
10/Mar/2023 68
31/Mar/2023 74
01/Apr/2023 83
18/Apr/2023 90
26/Apr/2023 87
07/May/2023 87
24/May/2023 104
I wanted to have a table with only Max date for each particular month in power query editor
Solved! Go to Solution.
pls try this
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Xc29CoUwDIbhe8ksNEl/HV0cBK+gOCi4ipzNu7cYe0hdH/LmyxmQzLQehpEtdEAeli4DsUYWZK/RxgdLPu5bRZck9xo9SZ4afPNg5vVXMTjJsUH5aUljdHV9OP+YrORJY4+yHprLdz2Wn9cH2WkkLEvLDQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Value = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Value", Int64.Type}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Year-Month", each Date.EndOfMonth([Date])),
#"Grouped Rows" = Table.Group(#"Added Custom", {"Year-Month"}, {{"Count", (x)=> Table.FromRecords({ Table.Max(x,"Date")})}}),
#"Removed Other Columns" = Table.SelectColumns(#"Grouped Rows",{"Count"}),
#"Expanded Count" = Table.ExpandTableColumn(#"Removed Other Columns", "Count", {"Date", "Value"}, {"Date", "Value"})
in
#"Expanded Count"
pls try this
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Xc29CoUwDIbhe8ksNEl/HV0cBK+gOCi4ipzNu7cYe0hdH/LmyxmQzLQehpEtdEAeli4DsUYWZK/RxgdLPu5bRZck9xo9SZ4afPNg5vVXMTjJsUH5aUljdHV9OP+YrORJY4+yHprLdz2Wn9cH2WkkLEvLDQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Value = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Value", Int64.Type}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Year-Month", each Date.EndOfMonth([Date])),
#"Grouped Rows" = Table.Group(#"Added Custom", {"Year-Month"}, {{"Count", (x)=> Table.FromRecords({ Table.Max(x,"Date")})}}),
#"Removed Other Columns" = Table.SelectColumns(#"Grouped Rows",{"Count"}),
#"Expanded Count" = Table.ExpandTableColumn(#"Removed Other Columns", "Count", {"Date", "Value"}, {"Date", "Value"})
in
#"Expanded Count"