cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
syale
Frequent Visitor

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 I need 1 day per row. How to do in Power Query.

 

I have tried to add columns and caculate out but I cannot get there

 

Before

Begin,End,Attr1,Attr2,Attr3
1/1/2020,1/1/2020,a,b,c
1/2/2020,1/2/2020,a,b,c
1/3/2020,1/5/2020,a,b,d

 

After

EventDate,Attr1,Attr2,Attr3
1/1/2020,a,b,c
1/2/2020,a,b,c
1/3/2020,a,b,d
1/4/2020,a,b,d
1/5/2020,a,b,d

 

Thanks in advance

1 ACCEPTED SOLUTION
camargos88
Super User III
Super User III

@syale ,

 

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"

 

Capture.PNG



Did I answer your question? Mark my post as a solution!

Proud to be a Super User!



View solution in original post

2 REPLIES 2
ziying35
Impactful Individual
Impactful Individual

Hi, @syale try the code below:

// output
let
    Source = Table.FromRecords(Json.Document(Binary.Decompress(Binary.FromText("i65WckpNz8xTslIyjNEHIiMDIwMlHSXXvBR0IceSkiJDoGAilG0EZCdB2cZAdrJSrQ6KaUaYphmRb5oxpmmmRJuWolQbCwA=", BinaryEncoding.Base64),Compression.Deflate))),
    fx = (row)=> List.Generate(()=>Date.From(row{0}), each _<= Date.From(row{1}), each Date.AddDays(_, 1), each {_}&List.Skip(row,2)),
    rows = Table.ToRows(Source),
    n = List.Count(rows),
    gen = List.Generate(()=>{null, {}}, each (_{0}<n)??true, each let i = _{0}+1??0 in {i, fx(rows{i})}, each _{1}),
    result = Table.FromRows(List.Combine(gen), {"EventDate"}&List.Skip(Table.ColumnNames(Source), 2))
in
    result

 

camargos88
Super User III
Super User III

@syale ,

 

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"

 

Capture.PNG



Did I answer your question? Mark my post as a solution!

Proud to be a Super User!



View solution in original post

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Top Kudoed Authors