Search code examples
powerquerym

How to select certain column in power query

I would like to choose a certain columns in power query, but not using their names. Ex. I can do this in R, by command: select. I'm wondering how i can do it in power query. I found some information here, but not all that I need.

Any idea, if I want to refer to more than one column?

It doesn't work if I write the code as below:

#"Filtered Part Desc" = Table.SelectRows ( 
#"Removed Columns3", 
each List.Contains(
    { "ENG", "TRANS" }, 
    Record.Field(_, Table.ColumnNames(#"Removed Columns3") { 5, 6, 7 }) 
)
)
Solution

  • Let's say I have this table and want to do a couple of things to it.

    Source table

    First, I want to change the column type of the second and last columns. We can use Table.ColumnNames to do this using simple indexing (which starts at zero) as follows:

    Table.TransformColumnTypes(
    Source,
    {
        {Table.ColumnNames(Source){1}, Int64.Type},
        {Table.ColumnNames(Source){3}, Int64.Type}
    }
)

    That works but requires specifying each index separately. If we want to unpivot these columns like this

    Table.Unpivot(#"Changed Type", {"Col2", "Col4"}, "Attribute", "Value")

    but using the index values instead we can use the same method as above

    Table.Unpivot(
    #"Changed Type", 
    {
        Table.ColumnNames(Source){1},
        Table.ColumnNames(Source){3}
    }, "Attribute", "Value"
)

    But is there a way to do this where we can use a single list of positional index values and use Table.ColumnNames only once? I found a relatively simple though unintuitive method on this blog. For this case, it works as follows:

    Table.Unpivot(
    #"Changed Type",
    List.Transform({1,3}, each Table.ColumnNames(Source){_}),
    "Attribute", "Value"
)

    This method starts with the list of positional index values and then transforms them into column names by looking up the names of the columns corresponding to those positions.

    Here's the full M code for the query I was playing with:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSlTSUTIE4nIgtlSK1YlWSgKyjIC4AogtwCLJQJYxEFcCsTlYJAXIMgHiKiA2U4qNBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Col1 = _t, Col2 = _t, Col3 = _t, Col4 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{Table.ColumnNames(Source){1}, Int64.Type},{Table.ColumnNames(Source){3}, Int64.Type}}),
    #"Unpivoted Columns" = Table.Unpivot(#"Changed Type", List.Transform({1,3}, each Table.ColumnNames(Source){_}), "Attribute", "Value")
in
    #"Unpivoted Columns"