Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello, I have online and offline conversions.
Examples:
google ads campaign 1 - 10 leads
phone line 1 - 10 leads
I would sum these leads into one column, but with some filters. For example: sum "campaign 1" + "phone line 1" into "campaign 1"; sum "campaign 2" + "phone line 2" into "campaign 2".
Is this possible?
Thank you.
Solved! Go to Solution.
Hi @Anonymous ,
assuming your model has Table1 and Table2, please paste this code into the advanced editor of a new query and follow the steps:
let
Source = Table1,
#"Split Column by Delimiter" = Table.SplitColumn(Source, "Campaign", Splitter.SplitTextByEachDelimiter({"_"}, QuoteStyle.Csv, true), {"Campaign.1", "Campaign.2"}),
#"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Campaign.1", type text}, {"Campaign.2", Int64.Type}}),
ConvertedTable1 = Table.RenameColumns(#"Changed Type",{{"Campaign.2", "no"}}),
Custom1 = Table2,
#"Split Column by Delimiter1" = Table.SplitColumn(Custom1, "Phone_line", Splitter.SplitTextByEachDelimiter({"_"}, QuoteStyle.Csv, true), {"Phone_line.1", "Phone_line.2"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"Phone_line.1", type text}, {"Phone_line.2", Int64.Type}}),
ConvertedTable2 = Table.RenameColumns(#"Changed Type1",{{"Phone_line.2", "no"}}),
#"Merged Queries" = Table.NestedJoin(ConvertedTable1, {"no"}, ConvertedTable2, {"no"}, "Renamed Columns1", JoinKind.LeftOuter),
#"Expanded Renamed Columns1" = Table.ExpandTableColumn(#"Merged Queries", "Renamed Columns1", {"Leads"}, {"Leads.Phone"}),
#"Inserted Addition" = Table.AddColumn(#"Expanded Renamed Columns1", "Addition", each [Leads] + [Leads.Phone], Int64.Type),
#"Removed Columns" = Table.RemoveColumns(#"Inserted Addition",{"Leads", "Leads.Phone"})
in
#"Removed Columns"
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
@Anonymous ,
It would be better if you could explain it with sample input and expected output.
Hi @Anonymous
I'm afraid I don't understand the question.
Please check out these links:
https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
and ideally upload some sample data so we can quickly mock up a solution for it:
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
Hello @ImkeF
Below are tables and results which I expect. Lead may be generated from the website or by calling. I know from what campaign is "offline" lead. If a visitor calls me on the phone line 2 is from campaign 2. The issue with mapping is (maybe), that are a few phone lines paired with more than one campaign. Those I have to split (next step).
Hope it is cleaner.
Table1
Campaign | Leads |
Campaign_1 | 10 |
Campaign_2 | 20 |
Table2
Phone_line | Leads |
Phone_line_1 | 20 |
Phone_line_2 | 30 |
The result should be:
Campaign | Leads |
Campaign_1 | 30 |
Campaign_2 | 50 |
Hi @Anonymous
It seems there is possible solution here.
Besides, you could create bridge table and link two tables with this table, then sum values from two tables.
If you have any other problem, feel free to let us know.
Best Regards
Maggie
Hi @Anonymous ,
assuming your model has Table1 and Table2, please paste this code into the advanced editor of a new query and follow the steps:
let
Source = Table1,
#"Split Column by Delimiter" = Table.SplitColumn(Source, "Campaign", Splitter.SplitTextByEachDelimiter({"_"}, QuoteStyle.Csv, true), {"Campaign.1", "Campaign.2"}),
#"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Campaign.1", type text}, {"Campaign.2", Int64.Type}}),
ConvertedTable1 = Table.RenameColumns(#"Changed Type",{{"Campaign.2", "no"}}),
Custom1 = Table2,
#"Split Column by Delimiter1" = Table.SplitColumn(Custom1, "Phone_line", Splitter.SplitTextByEachDelimiter({"_"}, QuoteStyle.Csv, true), {"Phone_line.1", "Phone_line.2"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"Phone_line.1", type text}, {"Phone_line.2", Int64.Type}}),
ConvertedTable2 = Table.RenameColumns(#"Changed Type1",{{"Phone_line.2", "no"}}),
#"Merged Queries" = Table.NestedJoin(ConvertedTable1, {"no"}, ConvertedTable2, {"no"}, "Renamed Columns1", JoinKind.LeftOuter),
#"Expanded Renamed Columns1" = Table.ExpandTableColumn(#"Merged Queries", "Renamed Columns1", {"Leads"}, {"Leads.Phone"}),
#"Inserted Addition" = Table.AddColumn(#"Expanded Renamed Columns1", "Addition", each [Leads] + [Leads.Phone], Int64.Type),
#"Removed Columns" = Table.RemoveColumns(#"Inserted Addition",{"Leads", "Leads.Phone"})
in
#"Removed Columns"
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
... enclosing the file as well.
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
@Anonymous ,
@ImkeF , can you help on this
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
118 | |
101 | |
71 | |
61 |