POWER QUERY -LOOP

[sudanaditya]

Dear All - I have a scenario as below where I have a  CRP /SIT/UAT START AND END DATE FOR DIFFERENT PROJECT  .I need to have a conditional query which would take the CRP start date and CRP end date and plot it against each date with CRP based on the logic ,like wise it should loop in for SIT AND UAT for each PROJECT ROW

 

[v-juanli-msft · Accepted solution]

Hi @sudanaditya 

Finally, i figure out with result below

Code in Advanced editor

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WKijKzzJU0lEyNNQ31DcyMLSEsC2Q2JZIbEMjGAeEYnUgBhhB+SQbExsLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [project = _t, #"CRP start date" = _t, #"CRP end date" = _t, #"SIT start date" = _t, #"SIT end date" = _t, #"UAT start date" = _t, #"UAT end date" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"project", type text}, {"CRP start date", type date}, {"CRP end date", type date}, {"SIT start date", type date}, {"SIT end date", type date}, {"UAT start date", type date}, {"UAT end date", type date}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"project"}, "Attribute", "Value"),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Other Columns", "Attribute", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Attribute.1", "Attribute.2", "Attribute.3"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Attribute.1", type text}, {"Attribute.2", type text}, {"Attribute.3", type text}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"Attribute.3"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Attribute.1", "type"}, {"Attribute.2", "s/e"}, {"Value", "date"}}),
    #"Grouped Rows" = Table.Group(#"Renamed Columns", {"project", "type"}, {{"min", each List.Min([date]), type date}, {"max", each List.Max([date]), type date}, {"all", each _, type table [project=text, type=text, #"s/e"=text, date=date]}}),
    #"Expanded all" = Table.ExpandTableColumn(#"Grouped Rows", "all", {"s/e", "date"}, {"all.s/e", "all.date"}),
    #"Added Custom" = Table.AddColumn(#"Expanded all", "Custom", each let End = [max] in
List.Generate(()=> 
[min], 
each Duration.Days(End-_)>=0, 
each Date.AddDays(_,1))),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
    #"Removed Columns1" = Table.RemoveColumns(#"Expanded Custom",{"all.date", "min", "max", "all.s/e"})
in
    #"Removed Columns1"

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-juanli-msft]

Hi @sudanaditya 

First i need to know your data struture, 

i assume you have table struture as below, then i can move to next step.

We could refer to these articles to create a list or table with dates from min date to the max date.

https://www.kasperonbi.com/automatically-ranged-date-table-using-dax-and-m/

https://medium.com/@Konstantinos_Ioannou/powerquery-dynamic-date-dimension-table-filtering-f9201cf87...

Next we can add the list/table above to the table, then filter with the condition so to get the final result.

Please tell me the correct struture of your data so that i can achieve the final output.

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.