Forum Discussion
EduardG
1 year agoFrequent Visitor
Transform a table using Power Query
Hello, I am trying to convert a table that data stored in this way: Name Start End Anna 01-Jan-2024 15-Mar-2024 Mike 16-Feb-2024 30-Apr-2024 Sean 01-Mar-2024 31-Mar-2024 t...
- 1 year ago
The following M code will add the missing month
List.Generate( () => Date.StartOfMonth([Start]), (current) => current <= [End], (current) => Date.AddMonths(current, 1) )So add a Custom column and past this code in your column, then you just have to format it and removed the start and end date
This is the complet code I used
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcszLS1TSUTIw1PVKzNM1MjAyAfIMTXV9E4sgvFidaCXfzOxUkLCZrltqEkyRsYGuYwGSouDUxDyISXC9QEVIvNhYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, Start = _t, End = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Start", type date}, {"End", type date}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each List.Generate( () => Date.StartOfMonth([Start]), (current) => current <= [End], (current) => Date.AddMonths(current, 1) )), #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"), #"Added Custom1" = Table.AddColumn(#"Expanded Custom", "Custom.1", each Date.ToText([Custom], "MMM - yyyy", "en-US")), #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Start", "End", "Custom"}) in #"Removed Columns"
Cookistador
Super User
1 year agoThe following M code will add the missing month
List.Generate(
() => Date.StartOfMonth([Start]),
(current) => current <= [End],
(current) => Date.AddMonths(current, 1)
)
So add a Custom column and past this code in your column, then you just have to format it and removed the start and end date
This is the complet code I used
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcszLS1TSUTIw1PVKzNM1MjAyAfIMTXV9E4sgvFidaCXfzOxUkLCZrltqEkyRsYGuYwGSouDUxDyISXC9QEVIvNhYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, Start = _t, End = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Start", type date}, {"End", type date}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each List.Generate(
() => Date.StartOfMonth([Start]),
(current) => current <= [End],
(current) => Date.AddMonths(current, 1)
)),
#"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
#"Added Custom1" = Table.AddColumn(#"Expanded Custom", "Custom.1", each Date.ToText([Custom], "MMM - yyyy", "en-US")),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Start", "End", "Custom"})
in
#"Removed Columns"