Dataformat error we reached the end of the buffer

Оглавление:

• Как исправить типичные ошибки dataformat.er в Power BI
• 1. DataFormat.Error: мы достигли конца буфера
• 3. Power BI dataformat.error неверное значение ячейки # имя / #ref

Видео: How to use Microsoft Power BI 2023

The following MWE Flask app produces valid JSON. The JSON is parsed correctly from a file, but produces the error DataFormat.Error: We reached the end of the buffer when invoked directly from a web service. Why? And how can the error be avoided?

To reproduce:

1. Minimal working example:

   Save the code below as mwe.py; set FLASK_APP=mwe.py; execute flask run:

from flask import Flask

app = Flask(__name__)

@app.route('/users/<user_id>')
def user(user_id):
    return {"user_id": user_id, "answer": [
        {"A": 1, "B": 2},
        {"A": 11, "B": 12},
        {"A": 21, "B": 22},
    ]}

1. Create the resulting JSON, e.g. using curl http://127.0.0.1:5000/users/foo -o c:tempfoo.json.

   Now create an Excel PowerQuery that reads the JSON file. To do that, use Data, Get Data, From Other Sources, Blank Query, and paste the code below (possibly adjusting the path). The file is imported without errors:

let
    Source = Json.Document(File.Contents("C:tempfoo.json")),
    #"Converted to Table" = Record.ToTable(Source),
    #"Filtered Rows" = Table.SelectRows(#"Converted to Table", each ([Name] = "answer")),
    #"Expanded Value" = Table.ExpandListColumn(#"Filtered Rows", "Value"),
    #"Expanded Value1" = Table.ExpandRecordColumn(#"Expanded Value", "Value", {"A", "B"}, {"Value.A", "Value.B"}),
    #"Filtered Rows1" = Table.SelectRows(#"Expanded Value1", each true)
in
    #"Filtered Rows1"

1. Now replace the first line of the PowerQuery pipeline with the web service. Now we get an error. Why?

   This still produces the correct table, but the error is visible in the «Queries and Connections» panel.

let
    Source = Json.Document(Web.Contents("http://127.0.0.1:5000/users/foo")),
    #"Converted to Table" = Record.ToTable(Source),
    #"Filtered Rows" = Table.SelectRows(#"Converted to Table", each ([Name] = "answer")),
    #"Expanded Value" = Table.ExpandListColumn(#"Filtered Rows", "Value"),
    #"Expanded Value1" = Table.ExpandRecordColumn(#"Expanded Value", "Value", {"A", "B"}, {"Value.A", "Value.B"}),
    #"Filtered Rows1" = Table.SelectRows(#"Expanded Value1", each true)
in
    #"Filtered Rows1"

We can quickly merge multiple excel files by using the append query function in Power Query. However, if the number of files is large, there will be more manual steps when creating a query for the first time. Today we share with you how to dynamically merge multiple Excel files in the same folder through Power Query.

Assume that we have 28 transaction data submitted by provincial branches and the data structures are the same.

We used Excel2016 for a demo. Click on “Data” – “From File” – from “Folder”

Select the folder where the data is located and click “OK”

All Excel file information is loaded. Here we just need to keep the “Content” column – select the “Content” column and click “Start” – “Delete other columns”.

Click on “Add Columns” – “Add Custom Columns”, enter Excel.Workbook([Content], true), and put the contents of Content in Excel.Workbook Extract it out.

The following is the second parameter of Excel.Workbook(). We specify True to implement the first line as the title by default. The action of lifting and filtering the header line later.

Look at the parameter requirements for Excel.Workbook():

The first parameter is we want Query Excel Workbook

The second parameter is an optional parameter. By using true, you can specify that the data uses the first line as the title.

Continue our case…

Click the expand button to the right of the newly created Custom column to expand all fields.

Select the Custom.Data column, then “Delete other columns”

Click Custom.Data Expand the button to expand the data.

Click OK to get the final summary results. We choose to load the data into the table to see the final merged results.

All provinces have their data merged correctly.

By using this method, we can quickly merge the same structure Excel under the same folder. And Power Query has recorded our steps. In the future, after updating the data, we only need to click “Refresh” to get the latest data merge results, once and for all.

Not only that, if new files from other provinces are added to this folder, Power Query will automatically merge the data. We added data from three provinces.

  Just refresh PowerQuery to get the latest merged data, is it very simple and practical?

PowerPivot workshop original article, please indicate the source. More PowerBI Information and Courses Welcome to the PowerPivot Workshop’s “Visualization from Excel to Power BI Data Analysis” series

Jose— October 17, 2018
|