Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi Friends,
I am new into Power BI. I have a table below. I need to concatenate the ID Mappings values based on the Formula Field. Formula Column Contains Line ID. For ex:we take 5th row, Formula column value is 3,4 , then i have to concatenate the ID Mapping values "R708001,R708002"
Line ID | Group Account | Group account description | Formula | ID Mapping |
1 | R701000 | Gross sales | R701000 | |
2 | R70100T | Gross saless | 1 | |
3 | R708001 | Commissions to retail partner | R708001 | |
4 | R708002 | Regul commission to retail partner | R708002 | |
5 | R70800T | Sales allowances | 3,4 | |
6 | R_NS | Net sales | 2,5 | |
7 | R601001 | Fish purchase | R601001 | |
8 | R603001 | Change in fish inventory | R603001 | |
9 | R601001T | Fish & and other sea products | 7,8 | |
10 | R601002 | Meat purchase | R601002 | |
11 | R603002 | Change in meat inventory | R603002 | |
12 | R601002T | Meat | 10,11 | |
13 | R601003 | Fruits & vegetables purchase | R601003 | |
14 | R603003 | Change in fruits & vegetables inventory | R603003 | |
15 | R601003T | Fruits & vegetables | 13,14 | |
16 | R601004 | Grocery purchase | R601004 | |
17 | R603004 | Change in grocery products inventory | R603004 | |
18 | R601004T | Grocery | 16,17 | |
19 | R601005 | Finished goods purchase | R601005 | |
20 | R603005 | Change in finished goods inventory | R603005 | |
21 | R601005T | Finished goods | 19,20 | |
22 | R601006 | Other products purchase | R601006 | |
23 | R603006 | Change in other product inventory | R603006 | |
24 | R601006T | Other products | 22,23 | |
25 | R_FOOD | Food | 9,12,15,18,21,24 |
|
Expected result:
Line ID | Group Account | Group account description | Formula | ID Mapping |
1 | R701000 | Gross sales | R701000 | |
2 | R70100T | Gross saless | 1 | R701000 |
3 | R708001 | Commissions to retail partner | R708001 | |
4 | R708002 | Regul commission to retail partner | R708002 | |
5 | R70800T | Sales allowances | 3,4 | R708001,R708002 |
6 | R_NS | Net sales | 2,5 | R701000,R708001,R708002 |
7 | R601001 | Fish purchase | R601001 | |
8 | R603001 | Change in fish inventory | R603001 | |
9 | R601001T | Fish & and other sea products | 7,8 | R601001,R603001 |
10 | R601002 | Meat purchase | R601002 | |
11 | R603002 | Change in meat inventory | R603002 | |
12 | R601002T | Meat | 10,11 | R601002,R603002 |
13 | R601003 | Fruits & vegetables purchase | R601003 | |
14 | R603003 | Change in fruits & vegetables inventory | R603003 | |
15 | R601003T | Fruits & vegetables | 13,14 | R601003,R603003 |
16 | R601004 | Grocery purchase | R601004 | |
17 | R603004 | Change in grocery products inventory | R603004 | |
18 | R601004T | Grocery | 16,17 | R601004,R603004 |
19 | R601005 | Finished goods purchase | R601005 | |
20 | R603005 | Change in finished goods inventory | R603005 | |
21 | R601005T | Finished goods | 19,20 | R601005,R603005 |
22 | R601006 | Other products purchase | R601006 | |
23 | R603006 | Change in other product inventory | R603006 | |
24 | R601006T | Other products | 22,23 | R601006,R603006 |
Could you please advise this.
Thanks
Vinoth
Solved! Go to Solution.
Hi Vinoth,
This query might be slow for large datasets. But it is the only way that I can think of to accomplish what you are looking to do.
Regards,
Mike
let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], #"Changed Type" = Table.TransformColumnTypes(Source,{{"Formula", type text}}), Transform = Table.TransformColumns(#"Changed Type",{{"Formula", each if Text.Trim(_) = "" then null else List.Transform(Text.Split(_,","), each Number.From(_) -1), type list}}), GenFormulaResult = List.Accumulate(Table.ToRecords(Transform), {} , (s,c) => s & {if c[Formula] = null then c[ID Mapping] else Text.Combine(List.Transform(c[Formula], each s{_}), ",") }), AddFormulaResult = Table.AddColumn(Transform, "Formula Result", each GenFormulaResult{[Line ID]-1}), #"Removed Columns" = Table.RemoveColumns(AddFormulaResult,{"Formula"}) in #"Removed Columns"
Hi @Vinothsusai , See one little wrinkle as I got my value from Group Account, will straighten that out. Here is first pass.
Let me know if you have any questions.
If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel
Concatenate = var Form1 = IF('Table'[Formula.1]<>BLANK(),'Table'[Formula.1]) var Form2 = IF('Table'[Formula.2]<>BLANK(), 'Table'[Formula.2]) var F1 = CALCULATE(MAX('Table'[Group Account]),Filter(All('Table'),'Table'[Line ID]=Form1)) var F2 = CALCULATE(MAX('Table'[Group Account]),Filter(All('Table'),'Table'[Line ID]=Form2)) var result = IF('Table'[ID Mapping] ="",CONCATENATE(F1, CONCATENATE(",",F2))) return result
Proud to be a Super User!
Hi @Vinothsusai ,
I see now that this calls for a loop. Skipped over the title. Yikes. Anyway, you have a start on this. Here is an article on looping with DAX. If you solve the rest of this, let me know, otherwise I will get back to this later.
Let me know if you have any questions.
If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel
Proud to be a Super User!
Hi Nathaniel,
Thank you for your reply.
Could you pelase advice this can be achieved through query editor(Power query) because i need to transform this Id Mapping column (",") concatenation to Expand new rows because i need to link this Id Mapping column to another table via Merge Queries.
Thanks
Vinoth SUSAINATHAN
Hi Vinoth,
This query might be slow for large datasets. But it is the only way that I can think of to accomplish what you are looking to do.
Regards,
Mike
let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], #"Changed Type" = Table.TransformColumnTypes(Source,{{"Formula", type text}}), Transform = Table.TransformColumns(#"Changed Type",{{"Formula", each if Text.Trim(_) = "" then null else List.Transform(Text.Split(_,","), each Number.From(_) -1), type list}}), GenFormulaResult = List.Accumulate(Table.ToRecords(Transform), {} , (s,c) => s & {if c[Formula] = null then c[ID Mapping] else Text.Combine(List.Transform(c[Formula], each s{_}), ",") }), AddFormulaResult = Table.AddColumn(Transform, "Formula Result", each GenFormulaResult{[Line ID]-1}), #"Removed Columns" = Table.RemoveColumns(AddFormulaResult,{"Formula"}) in #"Removed Columns"
Thank you very much.