Forum Discussion

Skemaz's avatar
Skemaz
Icon for Advocate II rankAdvocate II
9 years ago
Solved

Lost columns on expanding JSON

Hi Please view the two screen-shots below. I am trying to keep the column shown in the top screen but lose it when I convert to table. Any assistance will be appreciated. Thanks, Mark.    
Need Help
  • ImkeF's avatar
    ImkeF
    9 years ago

    Please check out this file: https://www.dropbox.com/s/bram6131s01nv1t/PBI_ExpandJson.pbix?dl=0

    You need to edit the query "Download" with a reference to your local drive

     

    You just have to decide whether to join the tables in the query-editor, which would create one big monster-table, or create a data model, which would be the more professional approach.

  • ImkeF's avatar
    ImkeF
    6 years ago

    Hi sunildatalytyx ,

    if the fields are not included in the first 1000 items of the expanded content, then they will not show up.

    In that case you have to add them to the M-code manually.

    Imagine the automatic expansion had just found "PRN", then you'd add SNR like shown below:

     

     

    Simply add them to the list with commas as separators.

     

Skemaz's avatar
Skemaz
Icon for Advocate II rankAdvocate II
9 years ago

Hi

The images posted individually below (seem a bit tiny posted as a single combined image).

Thanks, Mark.

 

 

  • Skemaz's avatar
    Skemaz
    Icon for Advocate II rankAdvocate II
    9 years ago

    Hi ImkeF

    Thank you very much for your assistance, however I'm still stuck with an almost identical error as when I tried the Youtube video solution ... "Expression error: We cannot covert a value of type Table to type Text."

    If you'd like any additonal information please let em know.

    Below are some screen-shots of what I see.

    Thanks, Mark.

     

     

    • ImkeF's avatar
      ImkeF
      Icon for Community Champion rankCommunity Champion
      9 years ago

      Yes, you have to edit the code to adjust it to your query:

      "Source" needs to be replaced by the name of the step that contains the table you want to operate this on (which is usually the name of the previous step) and "Column1" needs to be replaced by the name of your column "Value".

       

      You might find these links helpful:

       

      How to integrate M-code into your solution  -- Check out more PBI- learning resources here

       

       

       

      • Skemaz's avatar
        Skemaz
        Icon for Advocate II rankAdvocate II
        9 years ago

        Hi ImkeF

        Thanks again for your prompt reply and sorry for dragging this out.

        So I changed the line to:

        = Table.TransformColumns(Source, {{“Value”, each if Value.Is(_, type list) then _ else {_} }} )

        where "Value" is the actual name of the colum but I still get "invalid identifier".

        So "Source" is likely not the step name.

        I see the following steps and I assume those are also the step names. Am I correct?

        If so, how do I discover which step it is and if (say) the step is "Invoke Custom Function1", do I write the statement like this?

        = Table.TransformColumns("Invoke Custom Function1", {{“Value”, each if Value.Is(_, type list) then _ else {_} }} )

        I actually tried all of the names including the previous step as:

        = Table.TransformColumns("Changed Type", {{“Value”, each if Value.Is(_, type list) then _ else {_} }} )

        All of them throw and error "invalid idetifier" for {{"Value"

        I am most grateful for your assistance.

        Thanks, Mark.