Forum Discussion

Martin84098's avatar
Martin84098
New Member
3 years ago
Solved

HTML DATA

 
Hello,

I have the following problem.

I would like to prepare the data from the HTML file for further use.

I've done it before, but unfortunately I have no idea how to do it. Data see screenshot

 

Thhis is the Cod Datei....


THANKS...

html
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi Martin84098 ,

     

    You deepen the column in this step:

    You can cancel this step and then select the column and parse the text to JSON format.

    Parse text as JSON or XML - Power Query | Microsoft Learn

    All steps:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wio7JiympjlHKTIlRsjLSATHiCxKLUvNKgPwYJYMYJaBYXmJuKpgbnJmckVqUkZpZUlycnFGakQqWziyOL09NAiswBAvkpEF0G5mCuUXpEK6xcYxSrQ6yhcYYFhqhWWiM3wZjI0wbYvJilWJjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    #"Parsed JSON" = Table.TransformColumns(#"Changed Type",{},Json.Document),
    #"Expanded Column1" = Table.ExpandListColumn(#"Parsed JSON", "Column1"),
    #"Expanded Column2" = Table.ExpandRecordColumn(#"Expanded Column1", "Column1", {"id", "id_parent", "name", "is_web", "lft", "rgt"}, {"id", "id_parent", "name", "is_web", "lft", "rgt"})
in
    #"Expanded Column2"

    You can create a new blank query and paste the code into the advanced editor.

     

    Best Regards,
    Gao
    Community Support Team

     

    If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

    How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi Martin84098 ,

     

    You deepen the column in this step:

    You can cancel this step and then select the column and parse the text to JSON format.

    Parse text as JSON or XML - Power Query | Microsoft Learn

    All steps:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wio7JiympjlHKTIlRsjLSATHiCxKLUvNKgPwYJYMYJaBYXmJuKpgbnJmckVqUkZpZUlycnFGakQqWziyOL09NAiswBAvkpEF0G5mCuUXpEK6xcYxSrQ6yhcYYFhqhWWiM3wZjI0wbYvJilWJjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    #"Parsed JSON" = Table.TransformColumns(#"Changed Type",{},Json.Document),
    #"Expanded Column1" = Table.ExpandListColumn(#"Parsed JSON", "Column1"),
    #"Expanded Column2" = Table.ExpandRecordColumn(#"Expanded Column1", "Column1", {"id", "id_parent", "name", "is_web", "lft", "rgt"}, {"id", "id_parent", "name", "is_web", "lft", "rgt"})
in
    #"Expanded Column2"

    You can create a new blank query and paste the code into the advanced editor.

     

    Best Regards,
    Gao
    Community Support Team

     

    If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

    How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

    • Martin84098's avatar
      Martin84098
      New Member
      3 years ago

      Hello, I need a step by step guide.
      How to decompose a nice super table from the web import file. As in picture 1. I've gotten the data from picture two apart like this before.
      It was just a few clicks without much of a code. THANK YOU THANK YOU

      • Anonymous's avatar
        Anonymous
        Not applicable
        3 years ago

        Hi Martin84098 ，

         

        Please try select this column and click the "Parse Json" button.

        This will get a list:

        Then click the button in the upper right corner to expand to the row.

        Best Regards,
        Gao
        Community Support Team

         

        If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

        How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

  • PhilipTreacy's avatar
    PhilipTreacy
    Super User
    3 years ago

    Hi Martin84098 

     

    I'm not clear what you are asking to be done.

     

    Do you want the data in the 2nd image turned into the table in the 1st image?

     

    If so, you'll need to provide the data in the 2nd image.  We cant recreate all of that.

     

    If the JSON data comes from a location that you also need to connect to, is that something you need help with ?

     

    Regards

     

    Phil