My first Power BI query

[Eds1989]

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 · Accepted solution]

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]

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

[Eds1989]

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