Forum Discussion
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 |
to look like this:
| Name | Period |
| Anna | Jan-2024 |
| Anna | Feb-2024 |
| Anna | Mar-2024 |
| Mike | Feb-2024 |
| Mike | Mar-2024 |
| Mike | Apr-2024 |
| Sean | Mar-2024 |
It's basically for each name I want to have a row with the months when the person is in. Thanks for any suggestions how can it be done.
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"
3 Replies
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"Hi EduardG , another solution you can possibly look at. Thanks!
let
Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Start", type date}, {"End", type date}}),
Rows = List.Transform(Table.ToRows(#"Changed Type"), each List.Select(_, each not (try Date.From(_))[HasError])),
Numbers = List.Transform(Rows, each List.Transform(_, each Number.From(_))),
Dates = List.Transform(Numbers, each List.Transform({_{0}.._{1}}, each Date.From(_))),
List = List.Transform(Dates, each _),
Date = Table.TransformColumns(Table.AddIndexColumn(#"Changed Type"[[Name]],"Date",0,1),{"Date", each List{_}}),
Months = Table.AddColumn(Date, "Months", each List.Distinct(List.Transform(_[Date], each List.Transform({Text.Start(Date.MonthName(_),3), Date.Year(_)}, each Text.From(_))))),
Tables = Table.TransformColumns(Months,{"Months", each List.Transform(_, each Text.Combine(_,"-"))}),
Expand = Table.ExpandListColumn(Tables, "Months")[[Name],[Months]]
in
Expand
