Forum Discussion

Eds1989's avatar
Eds1989
New Member
8 years ago
Solved

My first Power BI query

Hi,   I'm trying to test Power BI to see if it can suit our needs for dashboard and report creation, but am hitting my head against a brick wall whilst playing with it on my own.   I have connect...
  • v-juanli-msft's avatar
    v-juanli-msft
    8 years ago

    Hi Eds1989

    As for your requirement "Create a query that returns only specific columns, where columns A, B and C are equal to some value”, i make a test as below.

    let
    Source = Excel.Workbook(File.Contents("C:\Users\maggiel\Desktop\case\6\6.13\first query.xlsx"), null, true),
    Table1_Table = Source{[Item="Table1",Kind="Table"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(Table1_Table,{{"col1", Int64.Type}, {"col2", Int64.Type}, {"col3", Int64.Type}, {"A", Int64.Type}, {"B", Int64.Type}, {"C", Int64.Type}}),
    keeprows=Table.SelectRows(#"Changed Type",each [A]=5 and [B]=5 and [C]=5)
in
keeprows

     

     

    Best Regards

    Maggie

v-juanli-msft's avatar
v-juanli-msft
Icon for Community Support rankCommunity Support
8 years ago

Hi Eds1989

As for your requirement "Create a query that returns only specific columns, where columns A, B and C are equal to some value”, i make a test as below.

let
    Source = Excel.Workbook(File.Contents("C:\Users\maggiel\Desktop\case\6\6.13\first query.xlsx"), null, true),
    Table1_Table = Source{[Item="Table1",Kind="Table"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(Table1_Table,{{"col1", Int64.Type}, {"col2", Int64.Type}, {"col3", Int64.Type}, {"A", Int64.Type}, {"B", Int64.Type}, {"C", Int64.Type}}),
    keeprows=Table.SelectRows(#"Changed Type",each [A]=5 and [B]=5 and [C]=5)
in
keeprows

 

 

Best Regards

Maggie

  • Eds1989's avatar
    Eds1989
    New Member
    8 years ago

    Thank you for this!

     

    Whilst I am not familiar with the query syntax for Power BI, this will obviously do the trick.

    I think one thing I was struggling with, was that my datasource was giving me an error that prevented all rows from showing, preventing me from filtering on my required values.

     

    After correcting the error at a database level, filtering through the GUI worked as expected too.

    I also spotted the "Choose columns" button, to only show the fields I was interested in.

     

    Am I correct in thinking, Power BI will always return ALL rows from a table, and then filter out the data? That seems inefficient compared to our other reporting tools, that filter directly in the qeury and only return the rows we have actually filtered for.

     

    Thanks!

    James