Transform Data to Clean Table

350z 

It looks like you're facing challenges in converting plain text data retrieved from an API into a clean table format in Power BI. To achieve your expected result, you can follow these steps:

1. **Retrieve Data from API:**
- Use the "Web" connector in Power BI to retrieve data from the API endpoint.
- Ensure that the data is retrieved correctly and appears as plain text in the Power Query Editor.

2. **Clean Data in Power Query Editor:**
- Use the appropriate delimiter (comma) to split the plain text data into columns. You can do this by using the "Split Column" feature in Power Query Editor.
- Remove any unwanted characters or rows, such as quotation marks or extra header rows, to clean up the data.

3. **Convert Text to Table:**
- After cleaning the data, convert it into a table format using the "From Text" option in Power Query Editor.
- Specify the delimiter (comma) and ensure that the first row is treated as headers.

4. **Transform Data Types:**
- Ensure that the data types of each column are correctly identified. Power Query Editor automatically detects data types, but you may need to manually adjust them if necessary.

5. **Load Data into Power BI:**
- Once you're satisfied with the data transformation and formatting, click "Close & Load" to load the data into Power BI as a table.

6. **Verify Results:**
- Verify that the loaded table in Power BI matches your expected result. Check that the headers are correctly assigned and that the data is formatted as rows and columns.

Here's a simplified example of how you can achieve this in Power Query Editor:

```m
let
// Retrieve data from API
Source = Web.Contents("https://api.example.com/data"),

// Convert plain text data to table
#"Converted to Table" = Csv.Document(Source, [Delimiter=",", Encoding=65001, QuoteStyle=QuoteStyle.None]),

// Promote headers
#"Promoted Headers" = Table.PromoteHeaders(#"Converted to Table", [PromoteAllScalars=true])
in
#"Promoted Headers"
```

This Power Query script retrieves data from the API, converts it into a table, and promotes the first row as headers. Adjust the delimiter, encoding, and other settings as needed based on your specific data format and requirements.

Did I answer your question? Mark my post as a solution! Appreciate your Kudos !!