none
When Importing from JSON url get [List] some cell values that are piped delimited RRS feed

  • Question

  • Hello, I kow vba but am totally new to using power query

    I have json file I can access from a url it looks like

     {
          "Organization Name": "xxxxx",
          "Issues": [
            "Arts, Culture & Humanities",
            "Children & Youth",
            "Civic Engagement"
          ],
          "Your specific area of focus": "",
          "Skills you can offer": [
            "Arts",
            "Communications / Marketing / Social Media",
            "Community Organizing"
          ],
          "What is your age range?": "55+",
          "Initial Date": "11/21/2017 19:24",
          "Last Date": "12/18/2017 15:19",
          "Description": "",
        },

    some questions are comma delimited 

    "Issues": [
            "Arts, Culture & Humanities",
            "Children & Youth",
            "Civic Engagement"
          ],

    There cell values when imported by power query show as [List]

    but I need where ever [List] shows for its cell value to be a piped delimited string of the list

    in this example it would be

    Arts, Culture & Humanities|Children & Youth|Civic Engagement
    Thanks for any help on this


    • Edited by Caposn Monday, December 18, 2017 3:33 PM
    Monday, December 18, 2017 3:31 PM

Answers

  • You can try the following:

    let Source = Json.Document(File.Contents(<JSONFilePath>)), ConvertedToTable = Record.ToTable(Source), TransformedColumn = Table.TransformColumns(ConvertedToTable, {"Value", each if Value.Type(_) = type list then Text.Combine(_, "|") else _}),
    RenamedColumn = Table.RenameColumns(TransformedColumn,{{"Value", "Sector"}})
    in
    RenamedColumn

    Note that the generic name "Value" for the column name is a result of the portion of the JSON text that you provided. However, looking at your table column, it appears that there is additional data that provides the actual name "Sector."

    If this is the case (i.e. you didn't rename the column in Power Query), then your script would be as follows:

    let
        Source = Json.Document(File.Contents(<JSONFilePath>)),
        ConvertedToTable = Record.ToTable(Source),
        TransformedColumn = Table.TransformColumns(ConvertedToTable, {"Sector", each if Value.Type(_) = type list then Text.Combine(_, "|") else _})
    in
    TransformedColumn



    Monday, December 18, 2017 4:24 PM

All replies

  • You can try the following:

    let Source = Json.Document(File.Contents(<JSONFilePath>)), ConvertedToTable = Record.ToTable(Source), TransformedColumn = Table.TransformColumns(ConvertedToTable, {"Value", each if Value.Type(_) = type list then Text.Combine(_, "|") else _}),
    RenamedColumn = Table.RenameColumns(TransformedColumn,{{"Value", "Sector"}})
    in
    RenamedColumn

    Note that the generic name "Value" for the column name is a result of the portion of the JSON text that you provided. However, looking at your table column, it appears that there is additional data that provides the actual name "Sector."

    If this is the case (i.e. you didn't rename the column in Power Query), then your script would be as follows:

    let
        Source = Json.Document(File.Contents(<JSONFilePath>)),
        ConvertedToTable = Record.ToTable(Source),
        TransformedColumn = Table.TransformColumns(ConvertedToTable, {"Sector", each if Value.Type(_) = type list then Text.Combine(_, "|") else _})
    in
    TransformedColumn



    Monday, December 18, 2017 4:24 PM
  • Colin, thank you for an answer.  I am sorry to ask but can you tell me how to add this.

    I am trying this in advanced editor, but getting error message

    let
        Source = Json.Document(Web.Contents("https://XXX.json")),
        #"Converted to Table" = Record.ToTable(Source),
        Value = #"Converted to Table"{1}[Value],
        #"Converted to Table1" = Table.FromList(Value, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
        #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table1", "Column1", {"Gender", "'Other' Gender", "Race/Ethnicity", "Race/Ethnicity - Fill in the blank", "Generation", "Country", "City", "LinkedIn", "Other Web Presence", "Website", "Sector", "'Other' Sector", "Sticky Issue", "'Other' Sticky Issue", "Interests (select all that apply)", "Published writings using HSD", "Publication Links or Citations", "Favorite Simple Rule", "HSD Models and Methods You Use OFTEN (check all that apply)", "About You", "HSD Models and Methods you would love to practice more with the support of a ""method mentor"" from the network", "Please share a story about your praxis or describe how you use human systems dynamics in your work?", "What is your burning question about theory and/or practice of HSD?", "Why do you use human systems dynamics theory, models, and methods?", "For you, how does HSD relate to other complexity approaches?", "For you, how does HSD relate to other change management approaches?", "Initial Date", "Last Date", "Description", "Email", "Id", "Image", "Label", "Project Name", "Segment", "sumApp Link", "Type"}, {"Gender", "'Other' Gender", "Race/Ethnicity", "Race/Ethnicity - Fill in the blank", "Generation", "Country", "City", "LinkedIn", "Other Web Presence", "Website", "Sector", "'Other' Sector", "Sticky Issue", "'Other' Sticky Issue", "Interests (select all that apply)", "Published writings using HSD", "Publication Links or Citations", "Favorite Simple Rule", "HSD Models and Methods You Use OFTEN (check all that apply)", "About You", "HSD Models and Methods you would love to practice more with the support of a ""me", "Please share a story about your praxis or describe how you use human systems dyn", "What is your burning question about theory and/or practice of HSD?", "Why do you use human systems dynamics theory, models, and methods?", "For you, how does HSD relate to other complexity approaches?", "For you, how does HSD relate to other change management approaches?", "Initial Date", "Last Date", "Description", "Email", "Id", "Image", "Label", "Project Name", "Segment", "sumApp Link", "Type"}),
        #"TransformedColumn" = Table.TransformColumns(ConvertedToTable, {"Sector", each if Value.Type(_) = type list then Text.Combine(_, "|") else _})
    in
        #"Expanded Column1"
        #"TransformedColumn


    • Edited by Caposn Monday, December 18, 2017 8:54 PM
    Monday, December 18, 2017 8:53 PM
  • Hi,

    You cannot have two steps after the "in" keyword. You need to remove #"Expanded Column1", and format TransformColumn after "in" correctly by adding a matching quote at the end. In practice, I do not use spaces in step names to avoid the annoyance, clutter and error prone use of hashes and quotes.


    Monday, December 18, 2017 9:35 PM
  • Hi Colin, it looks we are trapped in the same kind of discussion.
    Monday, December 18, 2017 9:45 PM
  • Sigh...
    Monday, December 18, 2017 9:53 PM