Matching each list in a List of Lists against a list from another List of Lists by index position

[danextian]

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

[v-yuta-msft]

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

[danextian]

Hi @v-yuta-msft

Can  you please elaborate about using a loop?

[OwenAuger]

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

[danextian]

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,"$#$"))

[OwenAuger · Accepted 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.

[danextian]

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.