Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi All,
I need help in figuring this out. What I am tyring to do is create a custom function that will create a table from a text string. I am stuck in my FieldsList variable.
let func = (MyString as text, RowDel as text, ColDel as text) as any=> let //splits text into rows by RowDel ColSplit = Text.Split(MyString, RowDel), //splits text by into columns by ColDel TextSplit = List.Transform(ColSplit , (x)=> Text.Split(x, ColDel) ), //returns values from text string assuming the values start from position 1 with interval of 2 ValuesList = List.Transform(TextSplit, (x)=> List.Alternate(x,1,1)), //returns field names from text string, those not found in ListValues are field names FieldsList = List.Transform(TextSplit, (x)=> List.RemoveMatchingItems(x, ValuesList) ), Im stuck in this part. How do i accesss each list in ValuesList without explicitly
stating the list position (ValuesList{0}) and match that list against
a list in TextSplit that has the same position as the list in ValuesList?
//creates a table from ListValues and ListFields TableFromLists = Table.FromColumns({FieldsList, ValuesList}), //transposes the created table TransposedTable = Table.Transpose(TableFromLists), //promotes first row to headers PromotedHeaders = Table.PromoteHeaders(TransposedTable, [PromoteAllScalars = true]) in PromotedHeaders
in func
Sample string: PartyName$#$Dane$#$SoldTo$#$123456$#$Country$#$United States$#$State$#$New York$@$PartyName$#$Dane$#$SoldTo$#$123456$#$Country$#$United States$#$State$#$New Yorks$#$Zip$#$90210
The string above should return a list containing two tables.
In the screnshot above, I used this formula to invoke the custom function but I want incorpoate the splitting into rows into the function.
List.Transform(Text.Split([string],"$@$"), (x)=> fnTableFromString(x,"$#$"))
Here's the original function that I am trying to modify:
let func = (MyString as text, Del as text) as table => let //splits text by delimiter TextSplit = Text.Split(MyString, Del), //returns values from text string assuming the values start from position 1 with interval of 2 ValuesList = List.Alternate(TextSplit,1,1), //returns field names from text string, those not found in ListValues are field names FieldsList = List.RemoveMatchingItems(TextSplit, ValuesList), //creates a table from ListValues and ListFields TableFromLists = Table.FromColumns({FieldsList, ValuesList}), //transposes the created table TransposedTable = Table.Transpose(TableFromLists), //promotes first row to headers PromotedHeaders = Table.PromoteHeaders(TransposedTable, [PromoteAllScalars = true]) in PromotedHeaders in func
Proud to be a Super User!
Solved! Go to Solution.
Ok - I'm pretty sure the function I posted earlier does that. Here's a simplified version of it, getting rid of the initial "let..."
There may well be other/better ways to do this 🙂
(MyString as text, RowDel as text, ColDel as text) as any=> let //splits text into rows by RowDel ColSplit = Text.Split(MyString, RowDel), //splits text by into columns by ColDel TextSplit = List.Transform(ColSplit , (x)=> Text.Split(x, ColDel) ), //returns values from text string assuming the values start from position 1 with interval of 2 ValuesList = List.Transform(TextSplit, (x)=> List.Alternate(x,1,1)), //returns field names from text string, assuming the values start from position 0 with interval of 2 FieldsList = List.Transform(TextSplit, (x)=> List.Alternate(x,1,1,1) ), ValuesAndFieldsList = List.Zip ( {ValuesList, FieldsList } ), //returns a list of tables TableFromLists = List.Transform ( ValuesAndFieldsList, each Table.FromRows({_{0}},_{1})) in TableFromLists
If I invoke this on
"PartyName$#$Dane$#$SoldTo$#$123456$#$Country$#$United States$#$State$#$New York$@$PartyName$#$Dane2$#$SoldTo$#$1234567$#$Country$#$Canada$#$State$#$Quebec"
I get a list of two tables as you've described.
Hi danextian,
I have searched the list functions but can't find a function which can meet your requirement and List.RemoveMatchingItems() doesn't take consideration of index. I'm afraid you should use loop.
In addtion, I recommend you to use R script instead of power query and try again.
Regards,
Jimmy Tao
Hi @v-yuta-msft
Can you please elaborate about using a loop?
Proud to be a Super User!
Hi @danextian
If I understand you correctly, the output you need is a list of tables, where each table has a single row corresponding to a "row" from the original text...hopefully got that right 🙂
I would actually suggest you use another List.Alternate to get the field names, then use List.Zip to stich the lists together into ValuesList/FieldsList pairs.
This worked at my end after some testing:
let func = (MyString as text, RowDel as text, ColDel as text) as any=> let //splits text into rows by RowDel ColSplit = Text.Split(MyString, RowDel), //splits text by into columns by ColDel TextSplit = List.Transform(ColSplit , (x)=> Text.Split(x, ColDel) ), //returns values from text string assuming the values start from position 1 with interval of 2 ValuesList = List.Transform(TextSplit, (x)=> List.Alternate(x,1,1)), //returns field names from text string, assuming the values start from position 0 with interval of 2 FieldsList = List.Transform(TextSplit, (x)=> List.Alternate(x,1,1,1) ), /*Create a list of ValuesList/FieldsList pairs, i.e. { { { Row1_Value1, Row1_Value2, ... }, { Row1_Field1, Row1_Field2, ... } }, { { Row2_Value1, Row2_Value2, ... }, { Row2_Field1, Row2_Field2, ... } }, ... { { RowN_Value1, RowN_Value2, ... }, { RowN_Field1, RowN_Field2, ... } } } */
ValuesAndFieldsList = List.Zip ( {ValuesList, FieldsList } ), //returns a list of tables TableFromLists = List.Transform ( ValuesAndFieldsList, each Table.FromRows({_{0}},_{1})) in TableFromLists in func
Hi @OwenAuger,
Thank you for the response. What I am trying to achieve is, if had this text tring, PartyName$#$Dane$#$SoldTo$#$123456$#$Country$#$United States$#$State$#$New York, I would be able to generate a list containing a table using a custom function,
PartyName | SoldTo | Country | State |
Dane | 123456 | United States | New York |
However if I had PartyName$#$Dane$#$SoldTo$#$123456$#$Country$#$United States$#$State$#$New York$@$PartyName$#$Dane2$#$SoldTo$#$1234567$#$Country$#$Canada$#$State$#$Quebec, I would want to be able to generate a list that this time contains two tables:
Table 1 | |||
PartyName | SoldTo | Country | State |
Dane | 123456 | United States | New York |
Table 2 | |||
PartyName | SoldTo | Country | State |
Dane2 | 1234567 | Canada | Quebec |
So $@$ will serve as a delimiter to split the text string into two or more tables. I already have the script (posted prior) to generate just one table. I'm aware I can just use the formula below but out I've been very curious how to achieve this 🙂
List.Transform(Text.Split([string],"$@$"), (x)=> fnTableFromString(x,"$#$"))
Proud to be a Super User!
Ok - I'm pretty sure the function I posted earlier does that. Here's a simplified version of it, getting rid of the initial "let..."
There may well be other/better ways to do this 🙂
(MyString as text, RowDel as text, ColDel as text) as any=> let //splits text into rows by RowDel ColSplit = Text.Split(MyString, RowDel), //splits text by into columns by ColDel TextSplit = List.Transform(ColSplit , (x)=> Text.Split(x, ColDel) ), //returns values from text string assuming the values start from position 1 with interval of 2 ValuesList = List.Transform(TextSplit, (x)=> List.Alternate(x,1,1)), //returns field names from text string, assuming the values start from position 0 with interval of 2 FieldsList = List.Transform(TextSplit, (x)=> List.Alternate(x,1,1,1) ), ValuesAndFieldsList = List.Zip ( {ValuesList, FieldsList } ), //returns a list of tables TableFromLists = List.Transform ( ValuesAndFieldsList, each Table.FromRows({_{0}},_{1})) in TableFromLists
If I invoke this on
"PartyName$#$Dane$#$SoldTo$#$123456$#$Country$#$United States$#$State$#$New York$@$PartyName$#$Dane2$#$SoldTo$#$1234567$#$Country$#$Canada$#$State$#$Quebec"
I get a list of two tables as you've described.
Hi @OwenAuger,
Thank you for the response. I haven't had the time to check your previous M script so I wasn't sure if it was working. I replied with what I had in memory. However, your latest code works like a charm. Thank you again.
Proud to be a Super User!
User | Count |
---|---|
141 | |
113 | |
104 | |
78 | |
64 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |