skip to main content
Power BI
    • What is Power BI
    • Why Power BI
    • Customer stories
    • Data visuals
    • Security
    • Power BI Desktop
    • Power BI Pro
    • Power BI Premium
    • Power BI Mobile
    • Power BI Embedded
    • Power BI Report Server
  • Pricing
    • Azure + Power BI
    • Microsoft 365 + Power BI
    • Dynamics 365 + Power BI
      • Energy
      • Healthcare
      • Manufacturing
      • Media
      • Retail
    • For analysts
    • For IT
      • Overview
      • Embedded analytics
      • Power BI visuals
      • Automation
      • Documentation
      • Community
    • Partners Overview
    • Solutions Partners
    • BI Specialized Partners
    • Power BI CSOs
    • Fabric Partner Community
    • Training
    • Getting started
      • Overview
      • Self-guided learning
      • Webinars
      • Documentation
      • Roadmap
      • Overview
      • Issues
      • Give feedback
    • Blog
    • Business intelligence topics
    • Overview
    • Forums
    • Galleries
    • Submit ideas
    • Events
    • User groups
    • Community blog
    • Register
    • ·
    • Sign in
    • ·
    • Help
    Go To
    • Power BI forums
    • Updates
    • News & Announcements
    • Get Help with Power BI
    • Desktop
    • Service
    • Report Server
    • Power Query
    • Mobile Apps
    • Developer
    • DAX Commands and Tips
    • Custom Visuals Development Discussion
    • Health and Life Sciences
    • Power BI Spanish forums
    • Translated Spanish Desktop
    • Power Platform Integration - Better Together!
    • Power Platform Integrations
    • Power Platform and Dynamics 365 Integrations
    • Training and Consulting
    • Instructor Led Training
    • Galleries
    • Community Connections & How-To Videos
    • COVID-19 Data Stories Gallery
    • Themes Gallery
    • Data Stories Gallery
    • R Script Showcase
    • Webinars and Video Gallery
    • Quick Measures Gallery
    • 2021 MSBizAppsSummit Gallery
    • 2020 MSBizAppsSummit Gallery
    • 2019 MSBizAppsSummit Gallery
    • Events
    • Ideas
    • Custom Visuals Ideas
    • Issues
    • Issues
    • Events
    • Upcoming Events
    • Community Engagement
    • T-Shirt Design Challenge 2023
    • Community Blog
    • Power BI Community Blog
    • Custom Visuals Community Blog
    • Community Support
    • Community Accounts & Registration
    • Using the Community
    • Community Feedback
    cancel
    Turn on suggestions
    Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
    Showing results for 
    Search instead for 
    Did you mean: 

    Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

    • Power BI forums
    • Galleries
    • Quick Measures Gallery
    • DAX's PATH function equivalent Custom Column in Po...

    DAX's PATH function equivalent Custom Column in Power Query

    09-24-2019 00:38 AM - last edited 09-24-2019 00:43 AM

    Zubair_Muhammad
    Community Champion
    17031 Views
    LinkedIn LinkedIn Facebook Facebook Twitter Twitter
    Zubair_Muhammad
    Zubair_Muhammad Community Champion
    Community Champion
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    DAX's PATH function equivalent Custom Column in Power Query

    ‎09-24-2019 12:38 AM

    This custom column formula in Power Query provides an equivalent of DAX's PATH function.

    If you have many levels (PATHLENGTH is greater than say 10), it can save you time having to create calculated columns to get each PATHITEM

    With Power Query, you can simply split the PATH into PATHITEMS with one click.

     

    Just change the text in Red Color font in below formula according to your column names and previous step name. See the picture for guidance

     

     

    =let //Define your columns below
     c=[Child],p=[Parent],mytable=#"Changed Type",pc="Parent",cc="Child" 
      in
    let mylist={c} & List.Generate(()=>[x=0,y=p,w=1],each [w] > 0,each [z=[y], 
    x=Table.Column(Table.SelectRows(mytable,each Record.Field(_,cc)=z),pc),y=x{0},w=List.Count(x)
    ],
    each [y])
            in
    Text.Combine(List.Reverse(List.RemoveItems(
    List.Transform(mylist,each Text.From(_)),{null,""})),"|")

     

     

    PATH.jpg

     


    Regards
    Zubair

    Please try my custom visuals
    • Hierarchical Bar Chart
    • Multiple Sparklines
    • Cross the River Game
    Preview file
    301 KB
    Replicating DAX's Path function in M LG.pbix
    Labels:
    • Labels:
    • Other
    Message 1 of 16
    17,031 Views
    11
    Reply
    • All forum topics
    • Previous Topic
    • Next Topic
    toum
    toum Helper I
    Helper I
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    ‎06-22-2023 07:06 AM

    Hello, I know this was posted a while ago, but I would be grateful if someone could help me. How can I extract only the last level of the path, as shown in the figure below? I should only leave the lines in red and get rid of the other lines.

    Preview file
    172 KB
    Message 16 of 16
    2,304 Views
    0
    Reply
    Scott_Parker
    Scott_Parker
    Frequent Visitor
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    ‎11-16-2022 08:08 AM

    I wrote one that could do ~100,000 rows in ~20 seconds. Appends a column "PATH" to a table. Inspired by this BIAccountant post: https://www.thebiccountant.com/2021/02/10/guest-post-using-list-accumulate-for-input-output-genealog... but modified to be recursive.

     

    A record at the top of the hierarchy should have a null in the Parent field. There is no loop protection, so you don't want any records to be their own parents, their own grandparents, etc.

     

    Note that you only invoke the function once on the table as whole, not row-by-row. Add a step along the lines of:

    = fnAddPath(#"Previous Step", "ParentID Column Name", "SelfID Column Name")

     

     

    (Self_Referential_Table as table,  Parent_Column_Name as text,  Self_Column_Name as text) =>
    let
        #"Renamed Columns" = Table.RenameColumns(Self_Referential_Table, {{Self_Column_Name, "0"}, {Parent_Column_Name, "1"}}),
        Buffered = Table.Buffer(Table.SelectColumns(
                Table.RenameColumns(Self_Referential_Table,{{Self_Column_Name, "Child"},{Parent_Column_Name, "Parent"}}),{"Child", "Parent"}))
    in
        let
            GetParents = (state as table, currentLevelFromLeaf as number) =>
            let
                NextParents = Table.ExpandTableColumn(
                                    Table.NestedJoin(state, {Text.From(currentLevelFromLeaf)},
                                    Buffered, {"Child"}, "NextLevel", JoinKind.LeftOuter),
                                "NextLevel", {"Parent"}, {Text.From(currentLevelFromLeaf + 1)}),
                result = if List.NonNullCount(Table.Column(NextParents,Text.From(currentLevelFromLeaf + 1))) = 0 then
                    [resultTable = NextParents, maxHeight = currentLevelFromLeaf]
                else
                    @GetParents(NextParents, currentLevelFromLeaf + 1)
            in
                result,
    
            //Reformat the Output
            ResultOutput = GetParents(#"Renamed Columns", 1),
            ListOfLevels = List.Transform(List.Numbers(Record.Field(ResultOutput, "maxHeight") + 1,Record.Field(ResultOutput, "maxHeight") + 2, -1), each Number.ToText(_)),
            OutputTable = Table.AddColumn(Record.Field(ResultOutput, "resultTable"), "PATH", 
                each Text.Combine(List.Transform(ListOfLevels, (x) => Record.Field(_, x)), "|")),
            OutputTable2 = Table.RemoveColumns(Table.RenameColumns(
                OutputTable, {{"0", Self_Column_Name}, {"1", Parent_Column_Name}}),
                List.RemoveMatchingItems(ListOfLevels,{"0","1"}))
        in
            OutputTable2

     

     

     

    Probably the final Reformatting could be done more cleverly and in fewer lines of code.

    Message 10 of 16
    5,293 Views
    1
    Reply
    mloyalka1996
    mloyalka1996
    Frequent Visitor
    In response to Scott_Parker
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    ‎11-16-2022 11:40 AM

    Hi Scott, Thank you for the code. I have a couple of question regarding the funvtion you have written. A small question what does it mean when u say "A topmost record should have a null in the Parent field". do i need to add a record ?

    Message 11 of 16
    5,278 Views
    0
    Reply
    Scott_Parker
    Scott_Parker
    Frequent Visitor
    In response to mloyalka1996
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    ‎11-16-2022 11:54 AM

    I rephrased it. I meant a record at the top of the hierarchy, meaning it has no parents, should have Parent = null, rather than Parent = Self because it would result in infinite recursion.

    Message 12 of 16
    5,271 Views
    1
    Reply
    mloyalka1996
    mloyalka1996
    Frequent Visitor
    In response to Scott_Parker
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    ‎11-16-2022 12:18 PM

    thanks for that Scott. I created a coloumn called task_final. so whenevr Parent=self i replaced the value of child to null or kept the child value. Now when i am running the function with the following parameters. I still get an error. Any suggestion?

    mloyalka1996_0-1668629911592.png

     

    Message 13 of 16
    5,266 Views
    0
    Reply
    Scott_Parker
    Scott_Parker
    Frequent Visitor
    In response to mloyalka1996
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    ‎11-16-2022 12:42 PM

    I have added clarification. Invoke the function on the table in one go, not for each row.

    Message 14 of 16
    5,251 Views
    1
    Reply
    mloyalka1996
    mloyalka1996
    Frequent Visitor
    In response to Scott_Parker
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    ‎11-16-2022 12:51 PM

    thank you scott for the clarification. I am bit new to power bi . I am trying to run function as a whole but I am not able to . It will be a big help if you can provide some help on how I can run the function as a whole

     

    Message 15 of 16
    5,248 Views
    0
    Reply
    Guido_Beulen
    Guido_Beulen
    Frequent Visitor
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    ‎08-04-2022 07:21 AM

    (Never mind: all others - pay attention to the previous step in the Query Editor which needs to be referenced... that was my error-cause. Performance is extremely show, though, using this Custom Column and then seperating it into levels for a hierarchy (I have eight levels in the hierarchy and a couple of thousand rows). Wonder if it will fix the issues I now face with Dax though!

     

    I keep getting an Error as a result, what am I doing wrong? 

    = Table.AddColumn(#"Functionele accounts filteren", "Aangepast", each let //Define your columns here
    mytable=#"Dim Medewerker",p="repto_id",c="res_id"
    in
    let mylist={Record.Field(_,c)} & List.Generate(()=>[x=0,y=Record.Field(_,p),w=1],each [w] > 0,each [z=[y], x=Table.Column(Table.SelectRows(mytable,each Record.Field(_,c)=z),p),y=x{0},w=List.Count(x)
    ],
    each [y])
    in
    Text.Combine(List.Reverse(List.RemoveItems(
    List.Transform(mylist,each Text.From(_)),{null,""})),"|"))

    Message 9 of 16
    6,597 Views
    0
    Reply
    Oscar_Mtz_V
    Oscar_Mtz_V Kudo Commander
    Kudo Commander
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    ‎04-18-2022 12:19 AM

    @Zubair_Muhammad , thanks a lot!
    Just a small note for those that might be working with records with orphan records (Parent = null), you might need to fill this with the same "child id". This code can come in handy:

    = Table.ReplaceValue(#"Replaced Errors",null, each _[Child],Replacer.ReplaceValue,{"Parent"})

    Cheers!

    Oscar

     

    Don't forget to follow my BI blog in www.bibb.pro

     

    Bibb Logo-03.png

     

     

    Message 7 of 16
    8,003 Views
    1
    Reply
    nokjaknow
    nokjaknow
    Regular Visitor
    In response to Oscar_Mtz_V
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    ‎11-07-2022 09:27 AM

    @Zubair_Muhammad @Oscar_Mtz_V  

    Do you know how to modify it to display list of Salary instead of values of Parent column, but path is still related to Child and Parent columns ?

    Message 8 of 16
    5,431 Views
    0
    Reply
    a1b1c1
    a1b1c1 Advocate I
    Advocate I
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    ‎12-27-2019 02:15 AM

     Hi @Zubair_Muhammad, @Daniil 
    Either of the steps are too time consuming, it's been running for about 45 minutes and still hasn't been able to complete this step. Any help?

    Message 5 of 16
    15,160 Views
    3
    Reply
    justlogmein
    justlogmein Helper III
    Helper III
    In response to a1b1c1
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    ‎12-21-2021 07:27 PM

    I have tried this method and you are correct, this is much too inefficient. I have only 6,000 rows and we are talking hours to run. Did you find another solution?

    Message 6 of 16
    9,268 Views
    0
    Reply
    Daniil
    Daniil Kudo Kingpin
    Kudo Kingpin
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    ‎09-24-2019 04:23 AM

    Good work! Just to simplify the formula a bit:

    let //Define your columns here
    mytable=ChangedType,p="Parent",c="Child" 
      in
    let mylist={Record.Field(_,c)} & List.Generate(()=>[x=0,y=Record.Field(_,p),w=1],each [w] > 0,each [z=[y], x=Table.Column(Table.SelectRows(mytable,each Record.Field(_,c)=z),p),y=x{0},w=List.Count(x)
    ],
    each [y])
            in
    Text.Combine(List.Reverse(List.RemoveItems(
    List.Transform(mylist,each Text.From(_)),{null,""})),"|")
    Message 2 of 16
    16,943 Views
    4
    Reply
    asoysal
    asoysal
    New Member
    In response to Daniil
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    ‎12-12-2019 04:53 AM

    Thanks to @Daniil and @Zubair_Muhammad . I created the custom column,with no syntax error in the Custom Column creation step. However I receive an Error in the table view. 

    See below:

     

    = let //Define your columns here
    mytable=#"Changed Type",p=[#"DirectReportTo Asso. No."],c=[#"Asso. No."]
    in
    let mylist={Record.Field(_,c)} & List.Generate(()=>[x=0,y=Record.Field(_,p),w=1],each [w] > 0,each [z=[y], x=Table.Column(Table.SelectRows(mytable,each Record.Field(_,c)=z),p),y=x{0},w=List.Count(x)
    ],
    each [y])
    in
    Text.Combine(List.Reverse(List.RemoveItems(
    List.Transform(mylist,each Text.From(_)),{null,""})),"|")

    Message 4 of 16
    15,528 Views
    0
    Reply
    Zubair_Muhammad
    Zubair_Muhammad Community Champion
    Community Champion
    In response to Daniil
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    ‎09-24-2019 04:42 AM

    Thanks @Daniil 


    Regards
    Zubair

    Please try my custom visuals
    • Hierarchical Bar Chart
    • Multiple Sparklines
    • Cross the River Game
    Message 3 of 16
    16,935 Views
    0
    Reply

    Power Platform

    • Overview
    • Power BI
    • Power Apps
    • Power Pages
    • Power Automate
    • Power Virtual Agents

    • Sign in
    • Sign up

    Browse

    • Solutions
    • Partners
    • Consulting Services

    Downloads

    • Power BI Desktop
    • Power BI Mobile
    • Power BI Report Server
    • See all downloads

    Learn

    • Guided learning
    • Documentation
    • Support
    • Community
    • Give feedback
    • Webinars
    • Developers
    • Blog
    • Newsletter

    © 2023 Microsoft

    Follow Power BI

    • Privacy & cookies
    • Manage cookies
    • Terms of use
    • Trademarks
    Consumer Privacy Act (CCPA) Opt-Out Icon Your Privacy Choices