cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper III
Helper III

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?
Capture.JPG

 

Thanks to all

Pedro

1 ACCEPTED SOLUTION

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

View solution in original post

12 REPLIES 12
Impactful Individual
Impactful Individual

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

 

22.png

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....

Hi, @pedroccamaraDBI 

 

Sorry, one letter is missing, should be Text.StartsWith

Super User III
Super User III

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

Capture.JPG

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 

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
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

View solution in original post

Yes @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:
Capture.JPG

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

Hello @pedroccamaraDBI 

 

simple words... 🙂 ... the logic is to use the material list and check every entry and trying to compare every entry with the material of the current row but using the length of the list entry.. because this is the only logic that work in your example. every match is listed the other get null. Then exclude the nulls end use the last entry.

 

For the second question. I'm not an expert on visualizations. But can you share a small part of your database and what exactly you want to see in the visualisation?

 

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

Super User III
Super User III

Hello @pedroccamaraDBI 

 

is there by the way a column for the level?

 

Jimmy

Helpful resources

Announcements
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Microsoft Ignite

Microsoft Ignite with Arun Ulag

Featured Session Drive Data Culture with Power BI: Vision, Strategy & Roadmap. Register here https://myignite.microsoft.com #MSIgnite #PowerPlatform #Power BI ​

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors