Power BI - Wide to long data - between Start End date - Multiple columns

[viktiw]

Hello everyone,
I am looking for a way to convert my data from wide to long format in Power BI
Used unpivot options but couldn't get the required results.
I also need date range (between dates) for each start and end date in below show format.
Actual Data

Required Output

 

[JGordon11]

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnE2NDAwVNJRMtQ3NNQ3MjAyArONDGBsI31jBNMQSdjQFMGGKY/VgZoIMcXQBMlEJGPMkExBNt0CyUSoeGwsAA==", BinaryEncoding.Base64), Compression.Deflate)), 
        let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [DOC_NO = _t, STAGE1_IN = _t, STAGE1_OUT = _t, STAGE2_IN = _t, STAGE2_OUT = _t, STAGE3_IN = _t, STAGE3_OUT = _t]),
    
    GetDate = (rec as record, colname as text) as number => Number.From(Date.From(Record.Field(rec,colname))),

    tbl1 = List.Accumulate({1..3}, Source, (s,c)=> let tfc = Text.From(c) in Table.AddColumn(s, "STAGE" & tfc, each 
                let 
                    colpref = "STAGE" & tfc & "_",
                    start = GetDate(_, colpref & "IN"),
                    stop =  GetDate(_, colpref & "OUT")
                in 
                    List.Transform({start..stop}, Date.From))
    ),
    
    tbl2 = Table.RemoveColumns(tbl1,List.Skip(Table.ColumnNames(Source),1)),
    tbl3 = Table.UnpivotOtherColumns(tbl2, {"DOC_NO"}, "STAGE", "DATE"),
    Result = Table.ExpandListColumn(tbl3, "DATE")
in
    Result

 

[viktiw]

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnE2NDAwVNJRMtQ3NNQ3MjAyArONDGBsI31jBNMQSdjQFMGGKY/VgZoIMcXQBMlEJGPMkExBNt0CyUSoeGwsAA==", BinaryEncoding.Base64), Compression.Deflate)),
        let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [DOC_NO = _t, STAGE1_IN = _t, STAGE1_OUT = _t, STAGE2_IN = _t, STAGE2_OUT = _t, STAGE3_IN = _t, STAGE3_OUT = _t]),
   
    GetDate = (rec as record, colname as text) as number => Number.From(Date.From(Record.Field(rec,colname))),

    tbl1 = List.Accumulate({1..3}, Source, (s,c)=> let tfc = Text.From(c) in Table.AddColumn(s, "STAGE" & tfc, each
                let
                    colpref = "STAGE" & tfc & "_",
                    start = GetDate(_, colpref & "IN"),
                    stop =  GetDate(_, colpref & "OUT")
                in
                    List.Transform({start..stop}, Date.From))
    ),
   
    tbl2 = Table.RemoveColumns(tbl1,List.Skip(Table.ColumnNames(Source),1)),
    tbl3 = Table.UnpivotOtherColumns(tbl2, {"DOC_NO"}, "STAGE", "DATE"),
    Result = Table.ExpandListColumn(tbl3, "DATE")
in
    Result

Hi Gordon,
That worked and gave me the requested output.
But I needed it for all the stages and all the documents (stage 1-5, DC1001-1005).
Aplogies for missing that in my original request.

 

[JGordon11]

The Source step is just creating some sample data for demonstration purposes. You will need to change it to refer to your source data.

For example if your data is coming from an excel table named Table1 change the Source step to:

Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],

If you have five stages rather than three then change the {1..3} to {1..5} in the tbl1 step.