Forum Discussion
Create Parent Hierarchy
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 AddParentCopy 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
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
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
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 AddParentCopy 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
JimmyYes Jimmy801 !!! It works beautifully !!
Thank you so much for your patience!!
P.S. It's not urgent now, but could you explain the code in "simple words"...?Hi again Jimmy801
The report is showing like this:Of course i'm not saying it's your fault but is there any way of not showing all the blanks here? If i select all but blanks i also will loose some values....
Thanks again