How to use the list with a list in PQ

[citizenbh]

Hi,
I apologize to my English.

I have stood on this issue, please help

#"Added Custom3" = Table.AddColumn(#"Added Custom4", "Replace", each Text.ReplaceRange([Text], List.Min(List.Select([Marker], each _ > [COLOR=#ff0000][B]10[/B][/COLOR])), 0, "Sign"))

Formula works fine with number.

How in the above expression number 10 replaced with list which is located in column [Position], so that the individual takes the value from the list.
(Columns [Position] and [Marker] are the list)

 

[citizenbh]

Here's a little explanation:

Link image: https://www.dropbox.com/s/d9umt0kxfgy4sco/Explanation.jpg?dl=0

 

[MarcelBeug]

Thanks for the explanation.

My suggestion is to split the text, then do the replacements and combine the results.

In this Excel workbook you can find the input, output and a set of query objects (queries, function, parameter, lists).
This video illustrates how it works.

 

[citizenbh]

Thank you, it would be good that can be downloaded file.

 

[MarcelBeug]

It was my intention to share the Excel file via OneDrive, but apparently that will only allow online editing and not downloading the file.
Someone must have thought about that...

So here you are for the codes.

Main Query Transform Text:
(note: the first steps create the actual input table; at step #"Added Custom1" that input table is created, so that would be your start for your table).

let
    Source = TransformTextParameter1,
    #"Converted to Table" = #table(type table[Text = text], {{Source}}),
    #"Added Custom" = Table.AddColumn(#"Converted to Table", "Marker", each Marker),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Position", each Position),
    #"Invoked Custom Function" = Table.AddColumn(#"Added Custom1", "NewText", each Transform([Text], [Marker], [Position])),
    #"Removed Columns" = Table.RemoveColumns(#"Invoked Custom Function",{"Text", "Marker", "Position"})
in
    #"Removed Columns"

Function Transform (the intern part being a copy of query Transform Example Text):

let
    Source = (TransformTextParameter1 as text, Marker as list, Position as list) => 
let
    Source = TransformTextParameter1,
    #"Converted to Table" = #table(type table[Text = text], {{Source}}),
    #"Added Custom2" = Table.AddColumn(#"Converted to Table", "SplitByPosition", each Splitter.SplitTextByPositions({0}&Position)([Text])),
    TableFromColumns = Table.FromColumns({{0}&Position,#"Added Custom2"[SplitByPosition]{0}},type table[Position = Int64.Type,TextPart = text]),
    #"Added Custom3" = Table.AddColumn(TableFromColumns, "ReplaceAt", (CurrentRow) => List.First(List.Skip(Marker, each _ < CurrentRow[Position] or _ <= 10))-CurrentRow[Position]),
    #"Added Custom4" = Table.AddColumn(#"Added Custom3", "NewTextPart", each if [ReplaceAt] = null or [ReplaceAt] > Text.Length([TextPart]) then [TextPart] else Text.ReplaceRange([TextPart],[ReplaceAt],1,"Sign")),
    Result = Text.Combine(#"Added Custom4"[NewTextPart])
in
    Result
in
    Source

List Marker:

let
    Source = {7,13,32,42,52,63}
in
    Source

List Position:

let
    Source = {10,30,50}
in
    Source

Parameter TransformTextParameter1:

"blablaaMaaaaaMaaaaaaaaaaaaaaaaaaMaaaaaaaaaMaaaaaaaaaMaaaaaaaaaaMaaaaaaaaa" meta [IsParameterQuery=true, Type="Text", IsParameterQueryRequired=true]

 

[citizenbh]

Thanks again for your efforts.
I did this by your instructions and it's OK, but the Text, Marker and Position columns are part of the starting table that has more records.
I'm trying applied function "Transform" to the fields of the table, but the do not success, although I tried to change sources.

 

[MarcelBeug]

With me it works with multiple records.

Does your table look like the table at the very start of the video I shared in my first post (apart from the fact that your table has multiple records and maybe additional columns, that would be no issue)?
So with a Text column and Marker and Position columns each with embedded lists?

What exactly do you mean with "not success"? Do you get errors? Can you share your code?

 

[citizenbh]

Hi Marcel,

Ok. I want to insert line feed when first appearance of SPACE happens after 60 characters, or after 60*(n-Rows)[LengthRow60] in TextBox in Power View .
You know the rest.
"Sign" is "#(lf)" in my formula:
= Text.ReplaceRange ([Text] List.Min (List.Select ([Marker], each _> 60)), 0, "Sign"))

But new problem happen:

Function "Text.PositionOfAny ([Text], {" "}, Occurrence.All)" returns only the first twenty occurrences.

Now comes your part of code.

Link file: https://www.dropbox.com/s/iy5xk623tgk642e/SampleListWithList_in_PQ.xlsx?dl=0

 

[MarcelBeug]

Hi,

Your remark about the first twenty occurrences is incorrect as I will show in this video

I adjusted the Transform function so it will put in #(lf) instead of "Sign" at a minimum of 60 positions.

You may be interested in a function I created some time ago, to split text into parts of a maximum of n positions (i.c. n=60).

I called it WrapText:

(TextString as text, LineLength as number) as list =>
        List.Generate(() => 
                  [TextPart      = if Text.Length(TextString) <= LineLength
                                   then TextString 
                                   else if Text.PositionOf(Text.Start(TextString,LineLength + 1)," ",Occurrence.Last) > -1
                                        then Text.Start(TextString,List.Min({LineLength + 1,Text.PositionOf(Text.Start(TextString,LineLength + 1)," ",Occurrence.Last)}))
                                        else Text.Start(TextString,List.Min({LineLength,Text.Length(TextString)})),
                   RemainingText = if Text.Length(TextString) <= LineLength
                                   then "" 
                                   else if Text.PositionOf(TextPart," ") > -1 
                                        then Text.Trim(Text.End(TextString,Text.Length(TextString)-Text.Length(TextPart)-1))
                                        else Text.Trim(Text.End(TextString,Text.Length(TextString)-Text.Length(TextPart)))],
                   each Text.Length([TextPart])>0,
                   each [TextPart      = if Text.Length([RemainingText]) <= LineLength
                                         then [RemainingText]
                                         else if Text.PositionOf(Text.Start([RemainingText],LineLength + 1)," ",Occurrence.Last) > -1
                                              then Text.Start([RemainingText],List.Min({LineLength + 1,Text.PositionOf(Text.Start([RemainingText],LineLength + 1)," ",Occurrence.Last)}))
                                              else Text.Start([RemainingText],List.Min({LineLength,Text.Length([RemainingText])})),
                         RemainingText = if Text.Length([RemainingText]) <= LineLength
                                         then ""
                                         else if Text.PositionOf(TextPart," ") > -1
                                              then Text.Trim(Text.End([RemainingText],Text.Length([RemainingText])-Text.Length(TextPart)-1))
                                              else Text.Trim(Text.End([RemainingText],Text.Length([RemainingText])-Text.Length(TextPart)))],
                   each [TextPart])

Code of the SpaceToLineFide query:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Added Custom3" = Table.AddColumn(Source, "LengthText", each Text.Length([Text])),
    #"Added Custom1" = Table.AddColumn(#"Added Custom3", "LengthRow60", each Number.RoundTowardZero(Text.Length([Text])/60)),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom1",{{"LengthRow60", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type1", "Marker", each Text.PositionOf([Text]," ", Occurrence.All )),
    #"Added Custom2" = Table.AddColumn(#"Added Custom", "Position", each List.Transform(List.Numbers(1,[LengthRow60]), each _ *60)),
    #"Added Custom4" = Table.AddColumn(#"Added Custom2", "Transformed", each Transform([Text],[Marker],[Position])),
    #"Invoked Custom Function" = Table.AddColumn(#"Added Custom4", "Wrapped", each WrapText([Text], 60)),
    CombinedText = Table.TransformColumns(#"Invoked Custom Function",{{"Wrapped", each Text.Combine(_, "#(lf)")}})
in
    CombinedText

Code of the Transform function:

let
        Source = (TransformTextParameter1 as text, Marker as list, Position as list) => 
let
        Source = TransformTextParameter1,
        #"Converted to Table" = #table(type table[Text = text], {{Source}}),
        #"Added Custom2" = Table.AddColumn(#"Converted to Table", "SplitByPosition", each Splitter.SplitTextByPositions({0}&Position)([Text])),
        TableFromColumns = Table.FromColumns({{0}&Position,#"Added Custom2"[SplitByPosition]{0}},type table[Position = Int64.Type,TextPart = text]),
        #"Added Custom3" = Table.AddColumn(TableFromColumns, "ReplaceAt", (CurrentRow) => List.First(List.Skip(Marker, each _ < CurrentRow[Position] or _ <= 60))-CurrentRow[Position]),
        #"Added Custom4" = Table.AddColumn(#"Added Custom3", "NewTextPart", each if [ReplaceAt] = null or [ReplaceAt] > Text.Length([TextPart]) then [TextPart] else Text.ReplaceRange([TextPart],[ReplaceAt],1,"#(lf)")),
        Result = Text.Combine(#"Added Custom4"[NewTextPart])
in
        Result
in
        Source

 

[citizenbh]

I hope that this will be of benefit to other members.

Thanks Maestro!