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
bvilten
Helper II
Helper II

Creating new column with concatenateX

Hello All,

I have been going in circles with this and finally decided to reach out. I have two tables and want to create a new table (I think open to Ideas) Applications is the first table System Dependencies is the second and last is the goal. The SystemDependenciesId column in the Applications table came from an extracted list using | as a deliminater. I tried using an expand to rows instead but was still unable to get the concantenated list column that I want.

Applications 
IDTitleSystemDependenciesId
5956A22|161|164|55|370
5957B 
5958C6|52|369
5959D22|161|164

 

Dependencies
IDTitle
1-
6System 6
22System 7
52System 8
55System 9
161System 10
164System 11
369System 12
370System 13

 

Goal   
IDTitleSystemDependenciesIdSysDepList
5956A22|161|164|55|370System 6, System 8, System 10, System 11, System 13
5957B  
5958C6|52|369System 6, System 8, System 12
5959D22|161|164System 6, System 10, System 11

 

TIA

1 ACCEPTED SOLUTION
parry2k
Super User
Super User

@bvilten add a new column in your application table using following DAX expression;

 

Dependency = 
VAR __len = PATHLENGTH ( Applications[SystemDependenciesId] ) 
VAR __tbl = 
SELECTCOLUMNS ( 
    ADDCOLUMNS  ( 
        GENERATESERIES ( 1, __len, 1 ), 
        "@ID", 
        PATHITEM ( Applications[SystemDependenciesId], [Value] ) 
    ), 
    "@ID", [@ID] 
)
VAR __dependency = 
CALCULATETABLE ( 
    VALUES ( 
        Dependency[Title] ), 
        TREATAS ( __tbl, Dependency[ID] ) 
    )
RETURN CONCATENATEX ( __dependency, [Title], "," )

 

Follow us on LinkedIn and YouTube.gif to our YouTube channel

 

Learn about conditional formatting at Microsoft Reactor

My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would  Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

 

Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

View solution in original post

12 REPLIES 12
parry2k
Super User
Super User

@bvilten well done. ofcourse it is good to understand the expression and can become useful in the future. Glad you have a solution in place. Cheers!! Don't hesitate to subscribe to my YouTube channel where I post interesting videos on Power BI. Good luck!

 

Follow us on LinkedIn and YouTube.gif to our YouTube channel

 

Learn about conditional formatting at Microsoft Reactor

My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would  Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

 

Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

parry2k
Super User
Super User

@bvilten there is somethingng else going on with the pbix file you are working with. it worked fine at my end when I used the sample data you provided. Share your pbix file using 1/g drive, remove sensitive information before sharing.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Yes you are absolutely correct. As I was trying to understand your solution rather than just cut and paste I found TreatAs is for use where no relationship exists. In my attempts to solve this I had created an intremediary table that related to both systemdependencies and application. Once I removed this both the measure and the column solution worked great. Thank you both.

bvilten
Helper II
Helper II

Changing the Dependency[ID] to text cleared the errors I was getting from both the measure and the add column DAX. However both now return blanks for data, excepting that the measure does return the correct number of commas per items in Application[SystemDependenciesId]

parry2k
Super User
Super User

@bvilten or change the expression as below:

 

Dependency = 
VAR __len = PATHLENGTH ( Applications[SystemDependenciesId] ) 
VAR __tbl = 
SELECTCOLUMNS ( 
    ADDCOLUMNS  ( 
        GENERATESERIES ( 1, __len, 1 ), 
        "@ID", 
        PATHITEM ( Applications[SystemDependenciesId], [Value] ) 
    ), 
    "@ID", IFERROR ( CONVERT ( [@ID], INTEGER ), BLANK () ) 
)
VAR __dependency = 
CALCULATETABLE ( 
    VALUES ( 
        Dependency[Title] ), 
        TREATAS ( __tbl, Dependency[ID] ) 
    )
RETURN CONCATENATEX ( __dependency, [Title], "," )

 

Follow us on LinkedIn and YouTube.gif to our YouTube channel

 

Learn about conditional formatting at Microsoft Reactor

My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would  Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

 

Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

parry2k
Super User
Super User

@bvilten what is ID column type in dependency table, make sure it is text.

Follow us on LinkedIn and YouTube.gif to our YouTube channel

 

Learn about conditional formatting at Microsoft Reactor

My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would  Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

 

Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

smpa01
Super User
Super User

@bvilten  you can use a single measure like this to achieve the result

Measure =
CONCATENATEX (
    ADDCOLUMNS (
        FILTER (
            GENERATE (
                Applications,
                ADDCOLUMNS (
                    GENERATESERIES ( 1, PATHLENGTH ( Applications[SystemDependenciesId] ) ),
                    "new", PATHITEM ( [SystemDependenciesId], [Value], TEXT )
                )
            ),
            [new] <> BLANK ()
        ),
        "_title",
            CALCULATE (
                MAX ( 'Dependencies'[Title] ),
                VAR _x = [new] RETURN TREATAS ( { _x }, 'Dependencies'[ID] )
            )
    ),
    [_title],
    ",",
    [_title], DESC
)

 

 

smpa01_0-1642432376195.png

 

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs
HotChilli
Super User
Super User

Is there something mysterious going on with the SysDepList column because a lot of them don't match up with the id from dependencies table? e.g. system 6 is not 22 in the firstline of the goal table.

--

Personally, I would use Dependencies as a dimension table, Applications as the fact table.  And I would split the SystemDependenciesId column 'by delimiter' to Rows.

After this, f you want to create measures to display the columns (as in the  Goal table) then concatenatex is the way to go.

 

Let me know what you think

Sorry about that, mysterious only in that I hand typed all the example tables Apearently without looking 😉

parry2k
Super User
Super User

@bvilten and here is the output 

parry2k_0-1642430575179.png

 

 

Follow us on LinkedIn and YouTube.gif to our YouTube channel

 

Learn about conditional formatting at Microsoft Reactor

My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would  Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

 

Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

parry2k
Super User
Super User

@bvilten add a new column in your application table using following DAX expression;

 

Dependency = 
VAR __len = PATHLENGTH ( Applications[SystemDependenciesId] ) 
VAR __tbl = 
SELECTCOLUMNS ( 
    ADDCOLUMNS  ( 
        GENERATESERIES ( 1, __len, 1 ), 
        "@ID", 
        PATHITEM ( Applications[SystemDependenciesId], [Value] ) 
    ), 
    "@ID", [@ID] 
)
VAR __dependency = 
CALCULATETABLE ( 
    VALUES ( 
        Dependency[Title] ), 
        TREATAS ( __tbl, Dependency[ID] ) 
    )
RETURN CONCATENATEX ( __dependency, [Title], "," )

 

Follow us on LinkedIn and YouTube.gif to our YouTube channel

 

Learn about conditional formatting at Microsoft Reactor

My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would  Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

 

Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Thank you all, Since I was looking for a new column I started with this solution. I keep getting this error

bvilten_0-1642441302674.png

Thinking that it didn't like the empty or blanks in the  SystemDependenciesId column I tried wrapping the whole thing in an IF( isblank(systemDependenciesId ), blank(), "Above solution") but that didn't work I also tried check for pathlength <= 0 and returning 1 that did not work either and got the same error. Since it caliming a problem with [static column] I wonder if the real problem is in the PATHITEM line, but either way not sure how to fix it.

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.