Parsing a list of name value pairs in Power BI

Question

Background

I need to create a Power BI report/dashboard from data that is locked up in a tightly firewalled PostgreSQL database. Connecting Power BI directly to the DB is not an option. I can however access Python scripts running on the same server so it is fairly trivial to write a simple extract/transform script and present the data as either XML or JSON.

The problem

I'm a Power BI newbie and I can't figure out how to transform my data into tables in Power BI desktop

Here's an example Source Table for the standard sales demo

Month | Count
_____________
Jan   | 100
Feb   | 150
Mar   | 200
...

And that's the table I want at the other end in Power BI

I've tried JSON:

{
    data: {
        sales: [
                {Jan:100},
                {Feb:150},
                {Mar:200}
               ]
          }
}

And I've tried XML:

<root>
    <data>
        <sales>
            <period>
                 <month>Jan</month>
                 <count>100</count>
            </period>
            <period>
                 <month>Feb</month>
                 <count>100</count>
            </period>
            <period>
                 <month>Mar</month>
                 <count>200</count>
            </period>
      </sales>
   </data>
</root>

Obviously this is a simplified version of the data I'm using but it illustrates the issue

I've tried to parse this using the UI. The data loads and I can drill down to the rows but can't figure out how to read in as a table - I end up with either a table of lists, a table with just one row or a table of tables

Carl Walsh · Accepted Answer · 2015-08-19 07:08:39Z

Power Query has a different representation for key-value-pair data (an M record) and tabular data (an M table).

Getting tables from your XML is easy if you use the Xml.Tables library function and drill down:

let
    Source = Xml.Tables("<root>
    <data>
        <sales>
            <period>
                 <month>Jan</month>
                 <count>100</count>
            </period>
            <period>
                 <month>Feb</month>
                 <count>100</count>
            </period>
            <period>
                 <month>Mar</month>
                 <count>200</count>
            </period>
      </sales>
   </data>
</root>"),
    Table = Source{0}[Table],
    Table1 = Table{0}[Table],
    Table2 = Table1{0}[Table],
    #"Changed Type" = Table.TransformColumnTypes(Table2,{{"month", type text}, {"count", Int64.Type}})
in
    #"Changed Type"

If you wanted to work with JSON, you should change the schema to a single JSON object instead a list of many objects.

let
    Source = Json.Document("{
    data: {
        sales: [{
                     Jan:100,
                     Feb:150,
                     Mar:200
                 }]
          }
}"),
    data = Source[data],
    sales = data[sales],
    sales1 = sales{0},
    #"Converted to Table" = Record.ToTable(sales1),
    #"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Name", "Month"}, {"Value", "Count"}})
in
    #"Renamed Columns"

The step right before Converted to Table is an M record, which might be more useful depending what you want to do with it. You can use Record.ToTable to make a two-column table.

That's what I was after! May you receive the blessing of many up-votes

Collectives™ on Stack Overflow

Parsing a list of name value pairs in Power BI

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related