Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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)
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".
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
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
Country | Company | Property | date | value |
b | cell | connections | ######## | 15000 |
b | cell | connections | ######## | 380970 |
b | cell | connections | ######## | 3714618 |
b | cell | connections | ######## | 3773000 |
b | cell | connections | ######## | 1711000 |
b | cell | connections | ######## | 3700000 |
b | cell | connections | ######## | 655000 |
b | cell | connections | ######## | 1195000 |
b | cell | connections | ######## | 3700000 |
b | cell | connections | ######## | 3602000 |
b | cell | connections | ######## | 893000 |
b | fon | connections | ######## | 2692000 |
b | fon | connections | ######## | 5134846 |
b | fon | connections | ######## | 5509876 |
b | fon | connections | ######## | 5447875 |
b | fon | connections | ######## | 2849900 |
b | fon | connections | ######## | 5399193 |
b | fon | connections | ######## | 4628400 |
b | fon | connections | ######## | 5739425 |
b | tel | connections | ######## | 2012000 |
b | tel | connections | ######## | 3148000 |
b | cell | market-share | ######## | 0.0202 |
b | cell | connections | ######## | 320143 |
b | tel | market-share | ######## | 0.2556 |
b | tel | market-share | ######## | 0.3473 |
b | tel | market-share | ######## | 0.3356 |
b | fon | connections | ######## | 2805000 |
b | fon | connections | ######## | 3191600 |
b | tel | connections | ######## | 2093000 |
b | fon | market-share | ######## | 0.4098 |
b | fon | market-share | ######## | 0.3967 |
b | fon | market-share | ######## | 0.5014 |
b | tel | connections | ######## | 1600000 |
b | tel | connections | ######## | 2047000 |
b | tel | connections | ######## | 2042000 |
b | cell | market-share | ######## | 0.0339 |
b | fon | connections | ######## | 3027200 |
b | fon | connections | ######## | 3697300 |
b | fon | connections | ######## | 3959300 |
b | cell | market-share | ######## | 0.0552 |
b | fon | market-share | ######## | 0.4043 |
b | fon | connections | ######## | 4100900 |
b | fon | market-share | ######## | 0.4289 |
b | fon | market-share | ######## | 0.4278 |
b | fon | market-share | ######## | 0.4397 |
b | tel | market-share | ######## | 0.2969 |
b | tel | market-share | ######## | 0.2937 |
b | fon | market-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")
Best regards,
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?
@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.
Please check the sample pbix file as attached.
Best regards,
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
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,
Hello,
Unfortunately, in my specific case, your suggestion didn't solve the issue.
Best