script or SQL request to select data of filter's

[POWER_MI]

Hi all, 

Have some question about Power BI. 

The asking is to selection some data based on some criteria that we fixe when we import excel file to power BI, i explain :

For example if you have Table coluumn like 

project A         Data Project A     Data Project A-1    Data Project A-2

.....                    ......

When you will import excel file on power  Bi you will need to select just the data need for example 

If project A = Value p1  and Data Project A= Value DP1 and Data Project A-1=DP1-1

So how to implemente this condition when we import excel on power BI may be with script R ou SQL sript and how to do it automatiquily without any action from user ?

thanks for help

[POWER_MI]

script on Power Query like 

let
Source = Excel.Workbook(File.Contents(....

if ..

[v-jingzhang · Accepted solution]

Hi @POWER_MI 

Please try the following codes, the key codes are #"Filtered Rows" step.

let
    Source = Excel.Workbook(File.Contents("C:\Users\xxxx\Desktop\ExcelData.xlsx"), null, true),
    Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(Sheet1_Sheet,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Project", type text}, {"Step", type text}, {"Code", type text}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type1", each (([Project] = "AAVy_ii" and [Step] = "Y1" and [Code] = "Co1" )) or ([Project] = "AAVy_2" and [Step] = "Y3") or ([Project] = "AAVy_3" and [Step] = "Y1"))
in
    #"Filtered Rows"

Kindly let me know if this helps.

Community Support Team _ Jing Zhang
If this post helps, please consider Accept it as the solution to help other members find it.

[POWER_MI]

the DB excel is on enclosed screen

[POWER_MI]

Hi all,  thanks for answer. Is it possible to make example for excel file like data base:

the condition is 

If project ID = AAVy_ii  then take Value when Step =Y1 and Co=1

If project ID = AAVy_2  then take Value when Step =Y3 

If project ID = AAVy_3  then take Value when Step =Y1

[PhilipTreacy]

Hi @POWER_MI 

You do this with Power Query.  When you import data you use PQ to edit and transform the data into just the subset that you want.

Even if that is a SQL database, PQ only requests the data you will need.  When you do the initial connection and preview the data it looks like it is pulling a lot more data thna you need but when the query is finished,PQ does what's called query folding and basically wites its own SQL that the database server runs to only transfer the data you want.

Further reading

https://docs.microsoft.com/en-us/power-query/power-query-what-is-power-query

https://exceleratorbi.com.au/what-is-power-query/

https://www.myonlinetraininghub.com/excel-power-query-course

https://exceleratorbi.com.au/how-query-folding-works/

Regards

Phil

---

If I answered your question please mark my post as the solution.
If my answer helped solve your problem, give it a kudos by clicking on the Thumbs Up.

[amitchandak]

@POWER_MI , if it is a databse, in connection you write SQL of your choice