Expanding List field to add rows but include the original row number (Tricky one here)

[Stephan_FL]

Ok I have a API Json data that includes a status fields that is a list of status for each day of the month from 1 to 30 (or 31) depending on the month I extract. What I need is to expand this list to include the status but also the original position/row number so I can match the status to the day of the month any thoughts:

Data in the List FieldDaily AV List (Original)If I just did a Expand to new rows

[Stephan_FL]

Thank you @JoeBarry that was just the direction I needed

[v-xinruzhu-msft]

Hi @Stephan_FL 

You can put the following code to advanced editor

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSlTSUTIE4uJKpVgdMrmZeWBuEpBphJCFcQnLxgIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", Int64.Type}, {"Column3", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Column1", "Column2"}, {{"Count", each _, type table [Column1=nullable text, Column2=nullable number, Column3=nullable text]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([Count],"Index",1,1)),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Count"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Columns", "Custom", {"Column3", "Index"}, {"Column3", "Index"}),
    #"Filtered Rows" = Table.SelectRows(#"Expanded Custom", each ([Index] = 1))
in
    #"Filtered Rows"

Output

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[JoeBarry · Accepted solution]

Hi @Stephan_FL 

Maybe try indexing.

I think this video will be able to help create a numbered index for each day. Group by the Machine ID 

https://www.youtube.com/watch?v=_PBX3RPXxHw

Hope this helps

Joe

If this post helps, then please Accept it as the solution