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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
AndrewPF
Helper V
Helper V

retain longest value of a group of columns for each row

Hello, 

I have some data which looks something like this: 

 filepath1filepath2filepath3
540xxxx\xxxxxxxxxxx\xxx\xxxxxxx\xxxxxxxxxxxxxx
40486xx\xx\xxxxx\xx\xxx\xxxxxxxx\xx\xxx
48604890xxxx\xxxxx\xxxxxxxxxxxxx\xxxxx\xxxxx\xxxxx

and I need to keep, for each row, the path with the maximum length. 

So, in this case, it would be: 

idfilepath
5401
404862
486048903

Any ideas? 

1 ACCEPTED SOLUTION

The _ is just a reference to the current row's values.  Please try this instead.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjUxUNJRqgCCmJgKBABzwAQWWaiQUqxOtJKJgYmFGVgApAKhA86DaURTA9ZrYQbUbYnmACgFswSbHExJbCwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Id = _t, filepath1 = _t, filepath2 = _t, filepath3 = _t]),
    #"Added Custom" = Table.AddColumn(Source, "FilePath", 
        let varColumns = List.Select ( Table.ColumnNames ( Source ), each Text.Contains ( _, "filepath" ) ) in
        each 
        let 
            varValues = Record.FieldValues ( Record.SelectFields ( _ , varColumns ) ),
            varResult = Text.AfterDelimiter (
                varColumns { List.PositionOf ( varValues, List.Max ( varValues ) ) },
                "filepath"
            )
        in varResult 
    )
in
    #"Added Custom"

View solution in original post

7 REPLIES 7
AndrewPF
Helper V
Helper V

Thank you. Marked as solution. 

Just one minor thing (and I've coded around it so it's not vital) - any idea why my results are coming out as text fields ".1", ".2" etc. instead of numbers? 

Do you have a dot in the file path column names? Like filepath.1, filepath.2?  If only numbers remain you can convert it to a number by wrapping the varResult like this:  

in Number.FromText ( varResult )

If there really is a dot in the column names then you also need to change "filepath" to "filepath." in the varResult equation.

 

AndrewPF
Helper V
Helper V

OK, I get it now - I think. 

Can I replace the " _ " in the Record.FieldValues function with a discrete list?  I don't really want to use List.RemoveFirstN because the columns in my data are subject to change on a regular basis. 

The _ is just a reference to the current row's values.  Please try this instead.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjUxUNJRqgCCmJgKBABzwAQWWaiQUqxOtJKJgYmFGVgApAKhA86DaURTA9ZrYQbUbYnmACgFswSbHExJbCwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Id = _t, filepath1 = _t, filepath2 = _t, filepath3 = _t]),
    #"Added Custom" = Table.AddColumn(Source, "FilePath", 
        let varColumns = List.Select ( Table.ColumnNames ( Source ), each Text.Contains ( _, "filepath" ) ) in
        each 
        let 
            varValues = Record.FieldValues ( Record.SelectFields ( _ , varColumns ) ),
            varResult = Text.AfterDelimiter (
                varColumns { List.PositionOf ( varValues, List.Max ( varValues ) ) },
                "filepath"
            )
        in varResult 
    )
in
    #"Added Custom"
AndrewPF
Helper V
Helper V

I probably should have been clearer.  

The data above is not everything in the table. 

It looks as if you are using List.RemoveFirstN because there is only one column before the "file path" columns - is that correct? 

Yes, that's correct.  When checking the length of the field values the purpose of List.RemoveFirstN is to omit the Id field, in the event it has the longest value. 

jennratten
Super User
Super User

Hello - this is how you can accomplish this.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjUxUNJRqgCCmJgKBABzwAQWWaiQUqxOtJKJgYmFGVgApAKhA86DaURTA9ZrYQbUbYnmACgFswSbHExJbCwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Id = _t, filepath1 = _t, filepath2 = _t, filepath3 = _t]),
    #"Added Custom" = Table.AddColumn(Source, "FilePath", 
        let varColumns = Table.ColumnNames ( Source ) in
        each 
        let 
            varValues = Record.FieldValues ( _ ),
            varResult = Text.AfterDelimiter (
                varColumns { List.PositionOf ( varValues, List.Max ( List.RemoveFirstN ( varValues, 1 ) ) ) },
                "filepath"
            )
        in varResult 
    )
in
    #"Added Custom"

jennratten_0-1679319603105.png

 

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors
Top Kudoed Authors