Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Extract multiple dates as rows from JSON field

Hello PowerBI community,    I could not find an answer anywhere on the forum, I am trying to extract dates and an associated values from a JSON field. For each row, I have one or muliple dates with...
Need Help
  • PhilipTreacy's avatar
    PhilipTreacy
    4 years ago

    Hi Anonymous 

     

    Download new sample PBIX file

     

    OK, you can extract the date and corresponding value using this query instead

     

    let
    Source = { [#"2021-02-13" = 7.08333333], [#"2021-03-13" = 7.08333333], [#"2021-04-13" = 7.08333333] },
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Added Custom1" = Table.AddColumn(#"Converted to Table", "Date", each Record.FieldNames([Column1]){0}),
    #"Added Custom" = Table.AddColumn(#"Added Custom1", "Value", each Record.Field([Column1], Record.FieldNames([Column1]){0}))
in
    #"Added Custom"

     

     

    Regards

     

    Phil

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Hi PhilipTreacy ,

 

Thanky ou for your answer. I tred that, but as I have more than 1000 different dates, I'm afraid all columns won't be created: 

 

 

Will all the columns be created even I could not select them because I was limited to the 1st 1000 rows? 

 

Best,

Julien