Forum Discussion

rhockman's avatar
rhockman
Brass Contributor
May 12, 2023
Solved

Power Query

Is there a way to extract these dates from one cell and add new rows for each date, easily in power query or another method?    
excel
  • Lorenzo's avatar
    Lorenzo
    May 12, 2023

    rhockman 

    another 365

    =LET(
  clean,  SUBSTITUTE(SUBSTITUTE(Table1[Date],"*","")," ",""),
  concat, LAMBDA(n,d, n & "," & SUBSTITUTE(d,",",";" & n & ",")),
  VSTACK(
    Table1[#Headers],
    TEXTSPLIT(TEXTJOIN(";",,MAP(Table1[Name],clean,concat)),",",";")
  )
)
Patrick2788's avatar
Patrick2788
Silver Contributor
May 12, 2023

rhockman 

A 365 solutions perhaps:

 

=REDUCE(
    {"Name", "Date"},
    SORT(UNIQUE(Table1[Name])),
    LAMBDA(a, v,
        LET(
            filtered, TEXTJOIN(",", , FILTER(Table1[Date], Table1[Name] = v)),
            split, SORT(TEXTSPLIT(filtered, , ",")),
            r, ROWS(split),
            VSTACK(a, HSTACK(EXPAND(v, r, , v), split))
        )
    )
)

Resources