locked
Expanding internal list as columns instead of rows RRS feed

  • Question

  • Hi,

    I'm pulling data from an API into power query. The data is from a sales system and shows sold orders and the result should be one row for each order. There are number of columns with records that are no problems expanding but there are also two columns with list that when I expand the first list adds two columns with 24 rows for each order, expanding the second lists addss three more columns and a total of 144 rows per order, and instead I need to unpivot this so that I turn these 144 rows into one row.

    Instead of the lists expanding as rows I would like them to add as columns using the for the first lists fieldId as names for the new columns and value as the value for each column. For the second list fieldId as column names and  valueDouble as values for each column.  So far I haven't been able to solve this, does anyone have a suggestion on how to do this?

    The code looks like below with the lines that I need help with in bold and the fields to be column names and values in italic.

    Thanks!

    Caj

    let
        Source = Json.Document(Web.Contents("https://XXXXXXXXX)),
        data = Source[data],
        #"Converted to Table" = Table.FromList(data, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
        #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"id", "description"}),
        #"Filtered Rows2" = Table.SelectRows(#"Expanded Column1", each [id] = 26433),
        #"Changed Type" = Table.TransformColumnTypes(#"Filtered Rows2",{{"date", type date}}),
        #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"date", "Salesdate"}}),
        #"Filtered Rows" = Table.SelectRows(#"Renamed Columns", each [Salesdate] >= #date(2017, 1, 1)),
        #"Expanded user" = Table.ExpandRecordColumn(#"Filtered Rows", "user", {"name", "role"}, {"user.name", "user.role"}),
        #"Expanded user.role" = Table.ExpandRecordColumn(#"Expanded user", "user.role", {"name"}, {"user.role.name"}),
        #"Expanded client" = Table.ExpandRecordColumn(#"Expanded user.role", "client", {"name"}, {"client.name"}),
        #"Expanded project" = Table.ExpandRecordColumn(#"Expanded client", "project", {"name"}, {"project.name"}),
        #"Expanded stage" = Table.ExpandRecordColumn(#"Expanded project", "stage", {"name"}, {"stage.name"}),
        #"Expanded orderRow" = Table.ExpandListColumn(#"Expanded stage", "orderRow"),
        #"Expanded orderRow1" = Table.ExpandRecordColumn(#"Expanded orderRow", "orderRow", {"custom", "discount", "id"}),
        #"Expanded orderRow.product" = Table.ExpandRecordColumn(#"Expanded orderRow1", "orderRow.product", {"name"}, {"orderRow.product.name"}),
        #"Removed Columns" = Table.RemoveColumns(#"Expanded orderRow.product",{"notes", "contact", "modDate", "clientConnection", "currencyRate", }),
        #"Changed Type1" = Table.TransformColumnTypes(#"Removed Columns",{{"value", Int64.Type}}),
        #"Filtered Rows1" = Table.SelectRows(#"Changed Type1", each ([stage.name] = "Avslutad - Order")),
        #"Expanded custom" = Table.ExpandListColumn(#"Filtered Rows1", "custom"),
        #"Expanded custom1" = Table.ExpandRecordColumn(#"Expanded custom", "custom", {"fieldId", "value", "valueInteger"}, {"custom.fieldId", "custom.value", "custom.valueInteger"}),
        #"Replaced Value" = Table.ReplaceValue(#"Expanded custom1","Ändring av befintligt avtal/Omförhandling","Omförhandling/Ändring av befintligt avtal",Replacer.ReplaceText,{"custom.value"}),
        #"Filtered Rows3" = Table.SelectRows(#"Replaced Value", each [id] = 26433),
        #"Changed Type2" = Table.TransformColumnTypes(#"Filtered Rows3",{{"closeDate", type date}}),
        #"Expanded orderRow.custom" = Table.ExpandListColumn(#"Changed Type2", "orderRow.custom"),
        #"Expanded orderRow.custom1" = Table.ExpandRecordColumn(#"Expanded orderRow.custom", "orderRow.custom", {"valueDouble", "value", "fieldId"}, {"orderRow.custom.valueDouble", "orderRow.custom.value", "orderRow.custom.fieldId"})
    in
        #"Expanded orderRow.custom1"

    Sunday, September 10, 2017 1:30 PM

Answers

  • Hi CastCS. We recently added "Extract Values..." to handle this exact situation. You should see it when you click on the double-arrow icon to the right of the column name.

    You can extract the List into a delimited column, and then split it to get separate columns.

    Ehren

    Thursday, September 14, 2017 10:40 PM

All replies

  • Not sure if I understood your request correctly, but with Record.FromList you can transform your lists to records which can expand to columns in one row.

    Imke Feldmann - MVP Data Platform (PowerBI) - http://www.TheBIccountant.com 

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful - Thanks!

    Monday, September 11, 2017 5:48 PM
  • Hi CastCS. We recently added "Extract Values..." to handle this exact situation. You should see it when you click on the double-arrow icon to the right of the column name.

    You can extract the List into a delimited column, and then split it to get separate columns.

    Ehren

    Thursday, September 14, 2017 10:40 PM