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

PhilipTreacy's avatar
PhilipTreacy
Super User
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

This is brilliant, thank you so much PhilipTreacy !