Forum Discussion

rhockman's avatar
rhockman
Copper 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)),",",";")
  )
)

5 Replies

Sort By
  • Lorenzo's avatar
    Lorenzo
    Silver Contributor
    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)),",",";")
  )
)
      • Lorenzo's avatar
        Lorenzo
        Silver Contributor
        May 13, 2023

        rhockman . You're welcome. Whatever option you chose, there's a link at the bottom to Mark as solution => this Helps people who Search - Thanks

  • 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