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

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

    • pedroccamaraDBI's avatar
      pedroccamaraDBI
      Post Partisan
      5 years ago

      Hi again Jimmy801 

      Let me give you my M code. You probably will know what's wrong here:

      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(Table1[GL SNC AccountID]),
      AddParent = Table.AddColumn
      (
      Table1,
      "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

       

      and it gives me an error : Expression.Error: A cyclic reference was encountered during evaluation.
      My table name is Table1 and my column name is GL SNC AccountID.
      Another thing, my account number id has only unique values

      Thanks a lot Jimmy

      • Jimmy801's avatar
        Jimmy801
        Community Champion
        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