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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
danextian
Super User
Super User

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

 

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.

table from string.png

 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

 






Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu.
1 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.


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

View solution in original post

6 REPLIES 6
v-yuta-msft
Community Support
Community Support

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?






Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu.

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

Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

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,

 

PartyNameSoldToCountryState
Dane123456United StatesNew 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   
PartyNameSoldToCountryState
Dane123456United StatesNew York
    
Table 2   
PartyNameSoldToCountryState
Dane21234567CanadaQuebec

 

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

 






Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu.

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.


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

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.






Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.