locked
Get and transform first data row as list of values RRS feed

  • Question

  • Hi guys,

    As newbie with Power Query I'd like to check if this is the right/best way to do this... Initial table looks like:

    I need to get the values from columns [Math] through [Sport] in row 1 and tranform them into a List (called WeightList below) for further use. Below is the part of my script that does it :

    ...
        IdxFromZero = Table.AddIndexColumn(SrcChange, "IdxFromZero", 0, 1),
    
        // Get & Tranform 1st data row as (Weight) list
        FirstDataRow = Table.SelectRows(IdxFromZero, each [IdxFromZero]=0),
        TblWeight = Table.Transpose(Table.SelectColumns(FirstDataRow,{"Math", "English", "History", "Sciences", "Eco", "Philo", "Sport"}), {"Weight"}),
        WeightList = TblWeight[Weight],
    ...

    I would highly appreciate any suggestion/recommendation re. how this kind of things should be done. Many thanks in advance

    Thursday, April 5, 2018 4:25 PM

Answers

  • Hey Thunderlight,

    The 2 first steps of the code are mainly me replicating your data set, so the one that does the trick is the 3rd step called "Personalizado1" which is the spanish way of saying "Custom1" and then the last step is done through the UI by clicking the "Conver to Table" button. 

    To be more precise, the most important part of the whole code is when I reference the previous code and then use the curly brackets and add a number inside those curly brackets. That tells Power Query what specific row to extract from that element (since it's a table, it'll output a record).

    I'd highly recommend that you copy/paste the whole code in a new query so you can see the step by step of how things flow.

    I don't think you're giving yourself enough credit - the code that you have is WAY past a Newbie level in Power Query. In most cases, a newbie wouldn't even touch M code and would only stay within the realm of the UI. I think that you're on your way to becoming an expert :) 

    Let me know if there's anything else that I could help you with so you can implement that code. In the meantime, you've given me a great idea for a new blog post about this type of navigation so I'm on it!

    • Marked as answer by Lz._ Monday, April 9, 2018 9:08 AM
    Sunday, April 8, 2018 9:03 PM
  • Alternatively,

    WeightList = Record.FieldValues(Record.SelectFields(SrcChange{0}, {"Math", "English", "History", "Sciences", "Eco", "Philo", "Sport"}))

    SrcChange{0} gets the first row from the ScrChange table. Each row in a table is evaluated as a record.

    Record.SelectFields(...) selects the fields that you want from the ScrChange{0} record.

    Record.FieldValues(...) then returns the values from the selected fields as a list.


    • Edited by Colin Banfield Sunday, April 8, 2018 9:36 PM
    • Marked as answer by Lz._ Monday, April 9, 2018 9:07 AM
    Sunday, April 8, 2018 9:33 PM

All replies

  • Hey

    Since you know exactly where that data is going to be (the first row) then you can use the Table Row Navigation and your code should look like this:

    let
        Origen = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCi4pTUnNK1HQVwhPzUzPKFHSUbIEYlMgNgFicyhtBMaxOggNhiBVeqZQNYamcMJQzxxMG4AIEz0jUxRtIIMMjSBKIBpBKoCWgI0yg8hCzFGKjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Class = _t, Math = _t, English = _t, History = _t, Sciences = _t, Eco = _t, Philo = _t, Sport = _t]),
        #"Tipo cambiado" = Table.TransformColumnTypes(Origen,{{"Class", type text}, {"Math", type number}, {"English", Int64.Type}, {"History", type number}, {"Sciences", type number}, {"Eco", type number}, {"Philo", type number}, {"Sport", type number}}),
        Personalizado1 = #"Tipo cambiado"{0},
        #"Convertido en tabla" = Record.ToTable(Personalizado1)
    in
        #"Convertido en tabla"

    sorry about the Spanish labels. Ended up using the wrong version of Power BI Desktop when I started this.

    Thursday, April 5, 2018 5:04 PM
  • Hey Miguel

    I mentioned "newbie" ;-). Whilst I appreciate your expertise and insight into this I'm afraid I'm not in a position (yet) to understand and implement your suggestion. For now I'll stick to :

    ...
        IdxFromZero = Table.AddIndexColumn(SrcChange, "IdxFromZero", 0, 1),
    
        // Get & Tranform 1st data row as (Weight) list
        FirstDataRow = Table.FirstN(SrcChange, 1),
        TblWeight = Table.Transpose(Table.SelectColumns(FirstDataRow,{"Math", "English", "History", "Sciences", "Eco", "Philo", "Sport"}), {"Weight"}),
        WeightList = TblWeight[Weight],
    ...

    Thanks very much again

    Sunday, April 8, 2018 8:55 AM
  • Hey Thunderlight,

    The 2 first steps of the code are mainly me replicating your data set, so the one that does the trick is the 3rd step called "Personalizado1" which is the spanish way of saying "Custom1" and then the last step is done through the UI by clicking the "Conver to Table" button. 

    To be more precise, the most important part of the whole code is when I reference the previous code and then use the curly brackets and add a number inside those curly brackets. That tells Power Query what specific row to extract from that element (since it's a table, it'll output a record).

    I'd highly recommend that you copy/paste the whole code in a new query so you can see the step by step of how things flow.

    I don't think you're giving yourself enough credit - the code that you have is WAY past a Newbie level in Power Query. In most cases, a newbie wouldn't even touch M code and would only stay within the realm of the UI. I think that you're on your way to becoming an expert :) 

    Let me know if there's anything else that I could help you with so you can implement that code. In the meantime, you've given me a great idea for a new blog post about this type of navigation so I'm on it!

    • Marked as answer by Lz._ Monday, April 9, 2018 9:08 AM
    Sunday, April 8, 2018 9:03 PM
  • Alternatively,

    WeightList = Record.FieldValues(Record.SelectFields(SrcChange{0}, {"Math", "English", "History", "Sciences", "Eco", "Philo", "Sport"}))

    SrcChange{0} gets the first row from the ScrChange table. Each row in a table is evaluated as a record.

    Record.SelectFields(...) selects the fields that you want from the ScrChange{0} record.

    Record.FieldValues(...) then returns the values from the selected fields as a list.


    • Edited by Colin Banfield Sunday, April 8, 2018 9:36 PM
    • Marked as answer by Lz._ Monday, April 9, 2018 9:07 AM
    Sunday, April 8, 2018 9:33 PM
  • hey!

    As promised, I went ahead and created a new blog post where I try to explain more about the types of navigation methods that Power Query has available. I've re-scheduled the ones that I have so this one can come out tomorrow morning.

    Here's the link to that blog post which should go live tomorrow at 9am UTC-5.

    Here's an image as a teaser

    image

    Monday, April 9, 2018 12:43 AM
  • Hey Miguel,

    Thanks very much for all your explanations. Steps now understood. I will have a read "a bit" more to understand the JSON part. Hopefully your blog post will help...

    THANKS again

    Monday, April 9, 2018 8:25 AM
  • WeightList = Record.FieldValues(Record.SelectFields(SrcChange{0}, {"Math", "English", "History", "Sciences", "Eco", "Philo", "Sport"}))

    @Colin,

    I guess that's what I was looking for. So thank you for that + the explainations (this really helps these days).
    FYI as I needed some flexibility re. the number of columns & names I ended up implementing :

    let
        Source = Excel.CurrentWorkbook(){[Name="tblTest"]}[Content],
    
        // Data table = All columns but first
        ColumnNames = List.Skip(Table.ColumnNames(Source), 1),
        ColumnsSelected = Table.SelectColumns(Source, ColumnNames),
        DataTable = Table.TransformColumnTypes(ColumnsSelected, List.Transform(ColumnNames, each {_, type number})),
    
        // First data row as Weight list
        WeightList = Record.FieldValues(Record.SelectFields(Source{0}, ColumnNames))
    
    in
        DataTable


    • Edited by Lz._ Monday, April 9, 2018 9:16 AM
    Monday, April 9, 2018 9:03 AM
  • Hey Miguel,

    Thanks very much for all your explanations. Steps now understood. I will have a read "a bit" more to understand the JSON part. Hopefully your blog post will help...

    THANKS again

    hey!

    The first 2 steps (the one with the json function and the 2nd one about changed type) are automatically created by Power Query inside of Power BI Desktop when you go through the 'Enter Data' experience.

    The blog post will help you understand how the {0} works and how you can use the right click of your mouse to do some drill down aka navigation operations.

    let
        Origen = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCi4pTUnNK1HQVwhPzUzPKFHSUbIEYlMgNgFicyhtBMaxOggNhiBVeqZQNYamcMJQzxxMG4AIEz0jUxRtIIMMjSBKIBpBKoCWgI0yg8hCzFGKjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Class = _t, Math = _t, English = _t, History = _t, Sciences = _t, Eco = _t, Philo = _t, Sport = _t]),
        #"Tipo cambiado" = Table.TransformColumnTypes(Origen,{{"Class", type text}, {"Math", type number}, {"English", Int64.Type}, {"History", type number}, {"Sciences", type number}, {"Eco", type number}, {"Philo", type number}, {"Sport", type number}}),
        Personalizado1 = #"Tipo cambiado"{0},
        #"Convertido en tabla" = Record.ToTable(Personalizado1),
        #"Promoted Headers" = Table.PromoteHeaders(#"Convertido en tabla", [PromoteAllScalars=true]),
        #"Student / Weight" = #"Promoted Headers"[#"Student / Weight"]
    in
        #"Student / Weight"

    Monday, April 9, 2018 5:32 PM