Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
pedroccamaraDBI
Post Partisan
Post Partisan

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

Jimmy801
Community Champion
Community Champion

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

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

Jimmy801
Community Champion
Community Champion

Hello @pedroccamaraDBI 

 

is there by the way a column for the level?

 

Jimmy

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

Top Solution Authors
Top Kudoed Authors