Forum Discussion

pedroccamaraDBI's avatar
pedroccamaraDBI
Post Partisan
5 years ago
Solved

Create Parent Hierarchy

Hey guys

I believe this is a tuff one. How can i creat a parent column out of 1 column like this?

 

Thanks to all

Pedro

Need Help
  • Jimmy801's avatar
    Jimmy801
    5 years ago

    Hello pedroccamaraDBI 

     

    try this

     

    let
Source = Excel.Workbook(File.Contents("C:\Users\Pedro\Desktop\Accounts.xlsx"), null, true),
Table1_Table = Source{[Item="Table1",Kind="Table"]}[Data],
#"Changed Type" = Table.TransformColumnTypes(Table1_Table,{{"GL SNC AccountID", type text}, {"GroupingCategory", type text}}),
BufferMaterial = List.Buffer(#"Changed Type"[GL SNC AccountID]),
AddParent = Table.AddColumn
(
#"Changed Type",
"Parent",
(row)=> List.Last(List.Select(List.Transform(BufferMaterial, (trans)=> if trans = Text.Start(row[GL SNC AccountID], Text.Length(trans)) and trans<> row[GL SNC AccountID] then trans else null),each _<> null))
)
in
AddParent

    Copy paste this code to the advanced editor in a new blank query to see how the solution works.

    If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
    Kudoes are nice too

    Have fun

    Jimmy

12 Replies

  • ziying35's avatar
    ziying35
    Impactful Individual
    5 years ago

    Hi, pedroccamaraDBI 

    Or try this:

     

    // output
let
    Source = Table.FromRecords(Json.Document(Binary.Decompress(Binary.FromText("i65W8k0sSS3KTMxRslIyNFaq1UETMcQiZGCETRBTswmmCBZFWKwAimFaARI0wK4W00FGGAqNDLEJYRXDLmhgYAz0UCwA", BinaryEncoding.Base64),Compression.Deflate))),
    lst_mt = List.Buffer(Source[Material]),
    fx=(val)=>
      let
         len = Text.Length(val),
         lst_m = List.Select(lst_mt, each Text.Length(_)<len and Text.StartWith(val, _))
      in Text.Combine(lst_m, "-"),
    result = Table.AddColumn(Source, "Parent", each fx([Material]))
in
    result

     

    • pedroccamaraDBI's avatar
      pedroccamaraDBI
      Post Partisan
      5 years ago

      Hello ziying35 
      Thank you very much for your answer but for some reason it shows an error:
      "Expression.Error: The name 'Text.StartWith' wasn't recognized. Mke sure it's spelled correctly."
      But as you may see it is....

  • Jimmy801's avatar
    Jimmy801
    Community Champion
    5 years ago

    Hello pedroccamaraDBI 

     

    found now a solution anyway. Was not even that hard 🙂

    Check it out

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjRWitUBUYYw2sAIzoLKmUApGBemFsgwQrAMkEShZhgZQik4jWAgsQwMjIFWxAIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Material = _t]),
    BufferMaterial = List.Buffer(Source[Material]),
    AddParent = Table.AddColumn
    (
        Source,
        "Parent", 
        (row)=> List.Last(List.Select(List.Transform(BufferMaterial, (trans)=> if trans = Text.Start(row[Material], Text.Length(trans)) and trans<> row[Material] then trans else null),each _<> null))
    )
in
    AddParent

     

    Copy paste this code to the advanced editor in a new blank query to see how the solution works.

    If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
    Kudoes are nice too

    Have fun

    Jimmy

    • pedroccamaraDBI's avatar
      pedroccamaraDBI
      Post Partisan
      5 years ago

      Hey Jimmy801 
      It seems to be the solution, and my file is much bigger than this one, in lines and in columns.
      I'm trying to add a custom column at the end of my query, name the column as Parent, paste the M code inside

      ((row)=> List.Last(List.Select(List.Transform(BufferMaterial, (trans)=> if trans = Text.Start(row[Material], Text.Length(trans)) and trans<> row[Material] then trans else null),each _<> null))

      I know that Material is my accountid column name. But that's it. 

      The file i've attach explain the accounts nature:
      GR = Accounts with only 2 digits and a sum row
      GA = Accounts with 3 or more digits, also a sum row
      GM = Accounts for movements with 3 or more digits

      Can you help me?

      Thanks a lot Jimmy801 

      • Jimmy801's avatar
        Jimmy801
        Community Champion
        5 years ago

        Hello pedroccamaraDBI 

         

        to make my code work you have also to add a new step like this, where you are referencing your table-column to buffer a list

        BufferMaterial = List.Buffer(Source[Material]),

         

        I can't see any file attached and I don't know what you mean. You asked about a parent-column, never saw something of a accounts and their lenght and sum rows

         

        If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
        Kudoes are nice too

        Have fun

        Jimmy