Forum Discussion
Extracting from column data to rows
I have 2 date columns (begin and end) with other attribute columns (attr1/2/3) that I need to expand out on dependent on begin and end date. The last date range in the series happens over 3 days and ...
Anonymous ,
Try this mcode:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtQ31DcyMDLQgTMSdZJ0kpVidUByRjA5I0w5Y5icKUIuRSk2FgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Begin,End,Attr1,Attr2,Attr3" = _t]), #"Demoted Headers" = Table.DemoteHeaders(Source), #"Split Column by Delimiter" = Table.SplitColumn(#"Demoted Headers", "Column1", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Column1.1", "Column1.2", "Column1.3", "Column1.4", "Column1.5"}), #"Promoted Headers" = Table.PromoteHeaders(#"Split Column by Delimiter", [PromoteAllScalars=true]), #"Changed Type3" = Table.TransformColumnTypes(#"Promoted Headers",{{"Attr1", type text}, {"Attr2", type text}, {"Attr3", type text}}), #"Changed Type with Locale" = Table.TransformColumnTypes(#"Changed Type3", {{"Begin", type date}, {"End", type date}}, "en-US"), #"Added Custom" = Table.AddColumn(#"Changed Type with Locale", "Custom", each List.Dates( [Begin], Duration.TotalDays([End] - [Begin]) + 1, #duration(1,0,0,0) )), #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"), #"Removed Columns" = Table.RemoveColumns(#"Expanded Custom",{"Begin", "End"}), #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Custom", "EventDate"}}), #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Attr1", type text}, {"Attr2", type text}, {"Attr3", type text}, {"EventDate", type date}}) in #"Changed Type"
Anonymous ,
Try this mcode:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtQ31DcyMDLQgTMSdZJ0kpVidUByRjA5I0w5Y5icKUIuRSk2FgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Begin,End,Attr1,Attr2,Attr3" = _t]),
#"Demoted Headers" = Table.DemoteHeaders(Source),
#"Split Column by Delimiter" = Table.SplitColumn(#"Demoted Headers", "Column1", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Column1.1", "Column1.2", "Column1.3", "Column1.4", "Column1.5"}),
#"Promoted Headers" = Table.PromoteHeaders(#"Split Column by Delimiter", [PromoteAllScalars=true]),
#"Changed Type3" = Table.TransformColumnTypes(#"Promoted Headers",{{"Attr1", type text}, {"Attr2", type text}, {"Attr3", type text}}),
#"Changed Type with Locale" = Table.TransformColumnTypes(#"Changed Type3", {{"Begin", type date}, {"End", type date}}, "en-US"),
#"Added Custom" = Table.AddColumn(#"Changed Type with Locale", "Custom", each List.Dates(
[Begin],
Duration.TotalDays([End] - [Begin]) + 1,
#duration(1,0,0,0)
)),
#"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
#"Removed Columns" = Table.RemoveColumns(#"Expanded Custom",{"Begin", "End"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Custom", "EventDate"}}),
#"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Attr1", type text}, {"Attr2", type text}, {"Attr3", type text}, {"EventDate", type date}})
in
#"Changed Type"