cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Sjoer_D Frequent Visitor
Frequent Visitor

Conditional fill with "yes/no" of new column

Hi all,

 

I am very new to power BI and currently I am trying to make an analysis of a extensive list of projects starting in 2016 and running up to 2023.

 

I want to indicate if for certian projects there is already (most likely) a contract in place by observing the activity in the years 2016-2018. In a new column I want to indicate this with a " yes" if there is activity (on year basis), and if there is activity during 2016-2018 fill the table with a yes for the whole range of years.

 

What I know did was indicate per indivdual year if there is activity, how do I know make sure for my whole projects the answer is yes if ther was activty? 

 

I was able to do this in Excel (simple formula) however there also seems no easy way of copying that new column in to my power Bi query and if I try to load it as a new query and append it I get "null" values. 

help.jpg


The code used for the individual year basis:

let
    Source = Table.Combine({#"Meic-rigs", #"Europe-rigs", #"Apac-rigs", #"Americas-rigs", #"Africa-rigs"}),
    #"Added Custom" = Table.AddColumn(Source, "Likely contracted?", each if ([Year] = 2016 or [Year] = 2017 or [Year] = 2018) and [Days] > 0 then "Yes" else "No"),
    #"Changed Type" = Table.TransformColumnTypes(#"Added Custom",{{"Likely contracted?", type text}}),
    #"Inserted Text Between Delimiters" = Table.AddColumn(#"Changed Type", "Text Between Delimiters", each Text.BetweenDelimiters(Text.From([Asset Approval Year], "nl-NL"), "2", "0"), type text),
    #"Removed Columns" = Table.RemoveColumns(#"Inserted Text Between Delimiters",{"Text Between Delimiters"})
in
    #"Removed Columns"

Thank you very much for reading, looking forward to advise!

6 REPLIES 6
Community Support Team
Community Support Team

Re: Conditional fill with "yes/no" of new column

HI, @Sjoer_D 

You may try to merge this table with projects table by year column (and Region column?)

and then expand the "Likely contracted? " column

http://radacad.com/append-vs-merge-in-power-bi-and-power-query

 

If it is not your case, please share sample pbix file or some data sample and expected output . You can upload it to OneDrive and post the link here. Do mask sensitive data before uploading.

 

Best Regards,

Lin

 

 

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Sjoer_D Frequent Visitor
Frequent Visitor

Re: Conditional fill with "yes/no" of new column

Hi @v-lili6-msft 

 

Merging and then expanding on "likely contracted" seems to work! 

 

Only thing I am wondering now, how would I have done this purely by using Power Bi instead of using excel? 

 

Nevertheless thanks for the help.

 

Regard,

 

Sjoerd

Community Support Team
Community Support Team

Re: Conditional fill with "yes/no" of new column

hi, @Sjoer_D 

Yes, You could use Merge Queries in Power Bi.

 

Click Home->External data->Edit Queries

6.JPG

Then you will enter the Query Editor view of Power Bi.

Now do "Merging and then expanding on "likely contracted"" operation in there.

 

7.JPG

 

here is the document for you refer to:

http://radacad.com/append-vs-merge-in-power-bi-and-power-query

 

Best Regards,

Lin

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Sjoer_D Frequent Visitor
Frequent Visitor

Re: Conditional fill with "yes/no" of new column

Hi Lin,

 

Sorry for the confusion, I managed to do the merging and exapanding through Power Bi.

 

I was actually wondering how I could get the column of "likely contracted" have "yes" as an output for the whole project time window on the condittion in one of the years 2016-2018 there was activity. Or is this you typically do in Excel and then import into Power Bi?

 

Regards,

 

Sjoerd

Community Support Team
Community Support Team

Re: Conditional fill with "yes/no" of new column

hi, @Sjoer_D 

You could merge two tables in Edit Queries like do it in excel as the same logic.

And you could also share some sample data and expected output, then I will show you the detailed steps that how to get it in power bi. 

 

Best Regards,

Lin

 

 

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Sjoer_D Frequent Visitor
Frequent Visitor

Re: Conditional fill with "yes/no" of new column

Hi Lin, 

 

My company is preventing to share a onedrive folder, please see if this link works.

https://wetransfer.com/downloads/2cc776dc028c28415d23dabec39dfbdb20190308084852/73445f

 

In the excel file you find the database, where the column 'acitivity' was created through Power BI using the code mentioned in my first post. What I want to do in Power Bi is fill the colum "likely contracted" but don't know how to do it, so I had to use Excel (see the formual used). 

 

I am wondering if instead of exporting my complete table to Excel, use a formual to fill the table and then Import the excel sheet and merge it as you explained above. I can do this all without leaving Power Bi and using excel.

 

Thanks so much for your time, this is really helpfull for me to learn logics of power BI!

 

Reg. Sjoerd