cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
wiczit
New Member

Transform.Columns with TextCombine

Hi everybody, hope you're doing great.

 

In advance, I am quite a neewbie in the advanced use of M language, apologies.

 

Summary

I need your help to concatenate a value within the same column (if its not null) or with other words to wrap a string value with double quotes without the creation of addtional- and removal of columns.

 

 

Scenario

- limited to use MS Excel 2013 + PQ Addon

Objective is to create and manage Testdata for an API Test with MS Excel.

PQ is used for further processing of Testdata and providing it finally in JSON format.

Postman (newman in particular) uses the JSON for test execution.

 

Workaround

Create an additional column ( for each column that requires transformation)

 

[section from advancedEditor]

addedColumn = Table.AddColumn(Source, "columnNameNew", each Text.Combine({"""", [columnName], """"}), type text)

Question

I am asking myself if there's a more efficient way like the combination of Table.TransformColumns with Text.Combine.

Unfortunately the following approach does not work.

Table.TransformColumns(A,{{"columnName",Text.Combine({"""", [columnName], """"}), type text}})

 

Appreciate any help links etc. espcially if this refers to better basic understanding of M lang.

 

 

Cheers, Martin

 

 

1 ACCEPTED SOLUTION

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WUtJRcgRiU6VYnWilJCALiEqKSlPBfGcgJwWIXdPSlWJjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [stringField1 = _t, stringField2 = _t, other = _t]),
    Transformed = List.Accumulate({"stringField1","stringField2"}, Source, (s,c) => Table.TransformColumns(s, {c, each if Text.Length(_)=0 then null else """"&_&""""}))
in
    Transformed

Untitled.png

View solution in original post

5 REPLIES 5
AlB
Super User III
Super User III

Hi @wiczit 

Can you share some sample data (in text-tabular format so that it can be copied) and the expected result?

 

SU18_powerbi_badge

Please accept the solution when done and consider giving a thumbs up if posts are helpful. 

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

 

Hi @AlB many thanks for your intrest to help me out.

 

wiczit_0-1620388243532.png

 

Given this simple table and this Code that works as the workaround:

 

 

let
    Quelle = Source,
    replaceNull_1 = Table.ReplaceValue(Quelle,"",null,Replacer.ReplaceValue,{"stringField"}),
    replaceNull_2 = Table.ReplaceValue(replaceNull_1,"",null,Replacer.ReplaceValue,{"stringField2"}),
    TextCombined1 = Table.AddColumn(replaceNull_2, "stringField_new1", each if [stringField] = null then null else Text.Combine({"""", [stringField], """"})),
    TextCombined2 = Table.AddColumn(TextCombined1, "stringField_new2", each if [stringField2] = null then null else Text.Combine({"""", [stringField2], """"})),
    SelectedColumns = Table.SelectColumns(TextCombined2,{"Target", "intField", "booleanField", "stringField_new1", "stringField_new2"}),
    RenamedColumns = Table.RenameColumns(SelectedColumns,{{"stringField_new1", "stringField"}, {"stringField_new2", "stringField2"}}),
    ReorderedColumns = Table.ReorderColumns(RenamedColumns,{"Target", "stringField", "stringField2", "intField", "booleanField"})
in
    ReorderedColumns

 

 

wiczit_1-1620388405522.png

 

My attempt to solve this more efficient instead does not work so well.

 

 

let
    Source = Source,
    transform = Table.TransformColumns(Source,each [stringField],Text.Combine("""",[stringField],""""))
in
    transform

 

 

Any advice ?

 

Best
M

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WUtJRcgRiU6VYnWilJCALiEqKSlPBfGcgJwWIXdPSlWJjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [stringField1 = _t, stringField2 = _t, other = _t]),
    Transformed = List.Accumulate({"stringField1","stringField2"}, Source, (s,c) => Table.TransformColumns(s, {c, each if Text.Length(_)=0 then null else """"&_&""""}))
in
    Transformed

Untitled.png

View solution in original post

Thanks @CNENFRNL that's the result I've expected.

But to be honest, this is not as straight forward as I've tought it will be.

 

Apreciating your support and as a learner I would like to understand your solution to have a (modest) chance to transfer this into new areas and to annoy you again (and again...).

 

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WUtJRcgRiU6VYnWilJCALiEqKSlPBfGcgJwWIXdPSlWJjAQ==", BinaryEncoding.Base64), Compression.Deflate)),

 I think the first part of the line creates just the table as shown and the fun part starts from here:

 

 let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [stringField1 = _t, stringField2 = _t, other = _t]),

What does this do? Can you pass me a #tag or a link for further info?

 

text


Transformed = List.Accumulate({"stringField1","stringField2"}, Source, (s,c) => Table.TransformColumns(s, {c, each if Text.Length(_)=0 then null else """"&_&""""}))
in
Transformed

 List Accumulate is executed in the List {} that contains StringField1 and stringField2.

the second parameter is the "seed", so seed is the previous step "Source".

the accumulator is then the third parameter that is built with the function Table.TransformColumns, where conditonally _ is cocantenated with double quotes.

Here I just don't understand the utilization of the parameters s,c and _?

Where do they come from? Do you have any links for further info sources on these?
At least the Table.TransformColumns help page does not tell how to do it.

 

Thanks again and in advance for any support.

Best M

Hi, there. So glad my proposal helps. As to those details, it requires some general understanding of the M language. I recommend this series of blogs as a primer,

https://bengribaudo.com/blog/2017/11/17/4107/power-query-m-primer-part1-introduction-simple-expressi...

in fact, thanks to this series, I got familiar with this fanscinating language.

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Top Solution Authors
Top Kudoed Authors