cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper I
Helper I

Keep only certain texts in a column and blank all the other texts in a column

Hi,
I would like to duplicate a column and keep certain texts in the copied column and leave all the other text blank (no filtering)

13 REPLIES 13
Highlighted
Super User I
Super User I

Re: Keep only certain texts in a column and blank all the other texts in a column

Hello @Q007 

 

what would be the logics to extract these "certain" texts?

 

Jimmy

Highlighted
Helper I
Helper I

Re: Keep only certain texts in a column and blank all the other texts in a column

Hi Jimmy,

The original column contains over 100 indicators, and I'd like that the copied column contains just one of those indicators that is called "market penetration".

Highlighted
Super User I
Super User I

Re: Keep only certain texts in a column and blank all the other texts in a column

Hello @Q007 

 

so you would need to check if your column contains this value otherwise empty?

if Text.Contains(Text.From([Column1]), "market penetration") = true then "market penetration" else ""

Copy paste this code to new custom column.

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

Highlighted
Helper I
Helper I

Re: Keep only certain texts in a column and blank all the other texts in a column

Hi Jimmy,

Thanks for the heads-up. although your suggestion works well I am still struggling to use line and stacked column chart as the values for both line and stacked column, namely "connections" and "market share" are on the same column.
In the an ideal situation, I should be able to use "connections" for stacked column values and "market share" for line values.

Cheers,

Q

Highlighted
Super User I
Super User I

Re: Keep only certain texts in a column and blank all the other texts in a column

Hello @Q007 

I don't fully get you.

Could you please make an example in order to understand your requirement fully

 

Jimmy

Highlighted
Helper I
Helper I

Re: Keep only certain texts in a column and blank all the other texts in a column

Hi Jimmy,

Please find below a random example which I like to use for a combo chart (column and line as primary and secondary Axis). 

On "value" column you will have the figures for  "connections" and "market share".  The date column will be used as X-axis.

the value of connections will be used for columns on the graph while the market share values will be used as the line. 
Best

Q

CountryCompanyPropertydatevalue
bcellconnections########15000
bcellconnections########380970
bcellconnections########3714618
bcellconnections########3773000
bcellconnections########1711000
bcellconnections########3700000
bcellconnections########655000
bcellconnections########1195000
bcellconnections########3700000
bcellconnections########3602000
bcellconnections########893000
bfonconnections########2692000
bfonconnections########5134846
bfonconnections########5509876
bfonconnections########5447875
bfonconnections########2849900
bfonconnections########5399193
bfonconnections########4628400
bfonconnections########5739425
btelconnections########2012000
btelconnections########3148000
bcellmarket-share########0.0202
bcellconnections########320143
btelmarket-share########0.2556
btelmarket-share########0.3473
btelmarket-share########0.3356
bfonconnections########2805000
bfonconnections########3191600
btelconnections########2093000
bfonmarket-share########0.4098
bfonmarket-share########0.3967
bfonmarket-share########0.5014
btelconnections########1600000
btelconnections########2047000
btelconnections########2042000
bcellmarket-share########0.0339
bfonconnections########3027200
bfonconnections########3697300
bfonconnections########3959300
bcellmarket-share########0.0552
bfonmarket-share########0.4043
bfonconnections########4100900
bfonmarket-share########0.4289
bfonmarket-share########0.4278
bfonmarket-share########0.4397
btelmarket-share########0.2969
btelmarket-share########0.2937
bfonmarket-share########0.4433
Highlighted
Super User I
Super User I

Re: Keep only certain texts in a column and blank all the other texts in a column

Hello @Q007 

 

try this solution. I suppose you can't have more then one connection and a market-share a day. I've chosen max-function when do the pivoting. But check out yourself what suits to you

let
	Source = #table
	(
		{"Country","Company","Property","date","value"},
		{
			{"b","cell","connections","43841","15000"},	{"b","cell","connections","43839","380970"},	{"b","cell","connections","43837","3714618"},	{"b","cell","connections","43843","3773000"},	
			{"b","cell","connections","43839","1711000"},	{"b","cell","connections","43842","3700000"},	{"b","cell","connections","43841","655000"},	{"b","cell","connections","43841","1195000"},	
			{"b","cell","connections","43840","3700000"},	{"b","cell","connections","43842","3602000"},	{"b","cell","connections","43843","893000"},	{"b","fon","connections","43841","2692000"},	
			{"b","fon","connections","43839","5134846"},	{"b","fon","connections","43842","5509876"},	{"b","fon","connections","43839","5447875"},	{"b","fon","connections","43841","2849900"},	
			{"b","fon","connections","43841","5399193"},	{"b","fon","connections","43843","4628400"},	{"b","fon","connections","43838","5739425"},	{"b","tel","connections","43840","2012000"},	
			{"b","tel","connections","43838","3148000"},	{"b","cell","market-share","43840","0,0202"},	{"b","cell","connections","43843","320143"},	{"b","tel","market-share","43842","0,2556"},	
			{"b","tel","market-share","43840","0,3473"},	{"b","tel","market-share","43842","0,3356"},	{"b","fon","connections","43839","2805000"},	{"b","fon","connections","43838","3191600"},	
			{"b","tel","connections","43839","2093000"},	{"b","fon","market-share","43838","0,4098"},	{"b","fon","market-share","43839","0,3967"},	{"b","fon","market-share","43841","0,5014"},	
			{"b","tel","connections","43839","1600000"},	{"b","tel","connections","43841","2047000"},	{"b","tel","connections","43838","2042000"},	{"b","cell","market-share","43841","0,0339"},	
			{"b","fon","connections","43841","3027200"},	{"b","fon","connections","43843","3697300"},	{"b","fon","connections","43842","3959300"},	{"b","cell","market-share","43842","0,0552"},	
			{"b","fon","market-share","43839","0,4043"},	{"b","fon","connections","43839","4100900"},	{"b","fon","market-share","43843","0,4289"},	{"b","fon","market-share","43840","0,4278"},	
			{"b","fon","market-share","43841","0,4397"},	{"b","tel","market-share","43842","0,2969"},	{"b","tel","market-share","43842","0,2937"},	{"b","fon","market-share","43841","0,4433"}

					}
	),
	ToDate= Table.TransformColumns
	(
		Source,
		{
			{
				"date",
				each Date.From(Number.From(_))
			},
			{
				"value",
				each Number.From(_, "de-DE")
			}
		}
	),
    Pivot = Table.Pivot(ToDate, List.Distinct(ToDate[Property]), "Property", "value", List.Max)
in
    Pivot

 

Copy paste this code to the advanced editor to see how the solution works. If this solution fits your need, copy and past a part of it and implement it in your query, or I could create a custom function what makes it easier to apply if you are not used that much to power query.

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

Highlighted
Community Support
Community Support

Re: Keep only certain texts in a column and blank all the other texts in a column

Hi @Q007 ,

 

We can also use two measures as the value of column and line filed to meet your requriement:

 

Connections - Column = CALCULATE(SUM('Table'[value]), 'Table'[Property]="connections")

 

Market-share Line = CALCULATE(SUM('Table'[value]),'Table'[Property] = "market-share")

 

2.jpg

 


Best regards,

 

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
Helper I
Helper I

Re: Keep only certain texts in a column and blank all the other texts in a column

Hi,

Thanks for your prompt reply. the SUM on "Market-share Line = CALCULATE(SUM('Table'[value]),'Table'[Property] = "market-share")" doesn't work as it goes beyond 100% for any given date. I guess the word "SUM" should be replaced by something like "max" or "absolute".

What do you think?

Best

Qmars

Helpful resources

Announcements
August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Upcoming Events

Upcoming Events

Wondering what events you could join or have an event to promote yourself? Check out our Upcoming Events.

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

We are thrilled to announce we will begin running a monthly webinar series named Power BI Dev Camp.

Top Solution Authors
Top Kudoed Authors