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
Vinothsusai
Helper III
Helper III

Add a custom column concatenate column values through looping

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 IDGroup AccountGroup account descriptionFormulaID Mapping
1R701000Gross sales R701000
2R70100TGross saless1 
3R708001Commissions to retail partner R708001
4R708002Regul commission to retail partner R708002
5R70800TSales allowances3,4 
6R_NSNet sales2,5 
7R601001Fish purchase R601001
8R603001Change in fish inventory R603001
9R601001TFish & and other sea products7,8 
10R601002Meat purchase R601002
11R603002Change in meat inventory R603002
12R601002TMeat10,11 
13R601003Fruits & vegetables purchase R601003
14R603003Change in fruits & vegetables inventory R603003
15R601003TFruits & vegetables13,14 
16R601004Grocery purchase R601004
17R603004Change in grocery products inventory R603004
18R601004TGrocery16,17 
19R601005Finished goods purchase R601005
20R603005Change in finished goods inventory R603005
21R601005TFinished goods19,20 
22R601006Other products purchase R601006
23R603006Change in other product inventory R603006
24R601006TOther products22,23 
25R_FOODFood9,12,15,18,21,24

 

 

 

Expected result:

 

Line IDGroup AccountGroup account descriptionFormulaID Mapping
1R701000Gross sales R701000
2R70100TGross saless1R701000
3R708001Commissions to retail partner R708001
4R708002Regul commission to retail partner R708002
5R70800TSales allowances3,4R708001,R708002
6R_NSNet sales2,5R701000,R708001,R708002
7R601001Fish purchase R601001
8R603001Change in fish inventory R603001
9R601001TFish & and other sea products7,8R601001,R603001
10R601002Meat purchase R601002
11R603002Change in meat inventory R603002
12R601002TMeat10,11R601002,R603002
13R601003Fruits & vegetables purchase R601003
14R603003Change in fruits & vegetables inventory R603003
15R601003TFruits & vegetables13,14R601003,R603003
16R601004Grocery purchase R601004
17R603004Change in grocery products inventory R603004
18R601004TGrocery16,17R601004,R603004
19R601005Finished goods purchase R601005
20R603005Change in finished goods inventory R603005
21R601005TFinished goods19,20R601005,R603005
22R601006Other products purchase R601006
23R603006Change in other product inventory R603006
24R601006TOther products22,23R601006,R603006

Could you please advise this.

 

 

Thanks

Vinoth

 

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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"

View solution in original post

5 REPLIES 5
Nathaniel_C
Super User
Super User

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

 

 

 

idmapping.PNG

 





Did I answer your question? Mark my post as a solution!

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.

 

LOOPING


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





Did I answer your question? Mark my post as a solution!

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

Anonymous
Not applicable

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. 

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