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 connected to an ODBC datasource we have on premise, and I see the query editor with a preview of results.

Some issues I have are;

  • In the preview, I cannot filter on a column with my desired value, as the value is not returned in the list of possible values in the preview. This seems to be because one row gives an error for a particular column about "value exceeding its max length or precision"
  • If I remove that troublesome column, the preview gives more results and I can filter on a column with all possible values
  • When I input a filter on a column, it tries to retrieve results, but then I get an error again relating to the troublesome column again even though I have already removed it from the query
  • Columns aren't sorted alphebetically, making it very difficult to find something I am looking for

From the point of having datasource connected, how can I:

Create a query that returns only specific columns, where columns A, B and C are equal to some value?

 

Many thanks

James

  • 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

3 Replies

  • Eds1989's avatar
    Eds1989
    New Member
    8 years ago

    Can anyone help me?

    The documentation Microsoft have provided around "Shaping Data", seems overly complex and not sure if it answers my basic queies.

     

    Thanks
    James

  • v-juanli-msft's avatar
    v-juanli-msft
    Community 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