Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to 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
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
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....
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
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:
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