Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Q007
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
Jimmy801
Community Champion
Community Champion

Hello @Q007 

 

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

 

Jimmy

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".

Jimmy801
Community Champion
Community Champion

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

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

Jimmy801
Community Champion
Community Champion

Hello @Q007 

I don't fully get you.

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

 

Jimmy

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

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.

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

Hi @Q007 ,

 

Could you please check the format of Values Column in the table? 

 

2.jpg

3.jpg

 

@Jimmy801 's query is a great solution, you can change the type of  Connections and maket-share from text to number to make it work.

 

4.jpg

 

Please check the sample pbix file as attached.


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.
Jimmy801
Community Champion
Community Champion

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

Hi Jimmy,

Thanks for your suggestions. If market-share and connections were expressed in absolute rather than aggregated values, the script would work. 

Best

Qmars

ExamplePNG.PNG

 

Hi @Q007 ,

 

How about the result after you follow the suggestions mentioned in my original post?Could you please provide more details about it If it doesn't meet your requirement?


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.

Hello,

Unfortunately, in my specific case, your suggestion didn't solve the issue.

Best

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors