[Help Requested] PowerQuery Formatting into Either JSON or CSV file

[aalbano]

Hi all,

I have this string of data from that I want to convert to either a CSV or JSON and eventually upload to SQL for further editing. 

Link: https://gist.github.com/Granitosaurus/0a9555c43a4261bed63ba57d095a5205 

The formatting is off as when I upload to PowerQuery, it is recognizing the data as a single column instead of the regular JSON/CSV formatting. 

Ideally, I would like the formatting to be broken down into columns rows as:

05/09/2022 - 19:00	0.0	0.0	0.0
05/09/2022 - 19:01	0.0	0.0	0.0
05/09/2022 - 19:02	0.0	0.0	0.0
05/09/2022 - 19:03	0.0	0.0	0.0
05/09/2022 - 19:04	0.0	0.0	0.0
...	...	...	...

I am rather new on PowerQuery, so I hope there's a possible solution for this instead of using something like a Python Regex to parse the data.

Any help is much appreciated. 

Thank you!

[ZhangKun]

let
    GetData = Web.Contents("https://gist.githubusercontent.com/Granitosaurus/0a9555c43a4261bed63ba57d095a5205/raw/3ab1b559dfa6d434d4b005be1bc7994345ddab65/some%2520graph"), 
    Readjson = Json.Document(GetData)[data], 
    ConvertRecord = List.Transform(Readjson, each {[label]} & [value]), 
    ResultTable = Table.FromRows(ConvertRecord)
in
    ResultTable

[v-sgandrathi · Accepted solution]

Hi @aalbano,
Hope you are doing well. 

Firstly ensure the file is valid JSON before renaming. If not, structure it correctly and use the JSON connector.
For raw or semi-structured data, use Power Query to clean and split it into rows/columns before converting to CSV.

To cleanse data in Power Query:

• Open Power BI, go to Get Data, select Text/CSV, and load the file or paste the data. Power Query will treat it as a single column initially.
• In Power Query, select the column, go to the Transform tab, and choose Split Column by Delimiter.
• After splitting, go to Transform, Trim to remove spaces and rename columns if needed.  Then save the cleaned and structured data as a CSV file.

By following the above steps you might achieve the desired output.

Please mark the helpful reply as the solution to assist others with similar queries. If you encounter further issues, don’t hesitate to reach out!

Thankyou for reaching out to the Microsoft Fabric Community Forum!

[v-sgandrathi]

Hello @aalbano.

We wanted to check in regarding your query, as we have not heard back from you. If you have resolved the issue, sharing the solution with the community would be greatly appreciated and could help others encountering similar challenges.

Thankyou for connecting with Microsoft Community Forum.

[v-sgandrathi]

 Hi @aalbano,

We are following up to see if your query has been resolved. Should you have identified a solution, we kindly request you to share it with the community to assist others facing similar issues.

If our response was helpful, please mark it as the accepted solution and provide kudos, as this helps the broader community.

Thanks for using Microsoft Fabric Community Forum.

[v-sgandrathi]

Hi  @aalbano,

We wanted to follow up since we haven't heard back from you regarding our last response. We hope your issue has been resolved.

If my answer resolved your query, please mark it as "Accept Answer" and select "Yes" if it was helpful.

If you need any further assistance, feel free to reach out.

Thank you for being a valued member of the Microsoft Fabric Community Forum!

[m_dekorte]

Hi @aalbano,

Your GitHub file doesn't have a JSON extension.

Saved with that extension let's you use the JSON Connector and data seems to be loading properly.

I hope this is helpful