Combining/Extract data from columns and row from merged to one column

[Watever]

May be there is another way to what I am trying to create, but it is getting me nuts trying to find how to do it.
I am verifying (for different products) if there is enough qty in stock of the same case pack as requested. If not, I need to show the user what other form pack is available.

I am merging two queries and I would like to combine two columns in one cell from the resulting merge.
I am able to merge one column by doing a list and a delimiter but not with 2 column.

What I want my table to output is

Produit	Nbr Pal Select	Ti	Hi	TiHi	Available
159820	6	5	9	45	51 x 1 46 x 1 41 x 1 35 x 1 113 x 1 51 x 119 45 x 34
…	…	…	…	…	…

This is what my ordered product look after a merge.

And this is what inside my Rsv_EXT column of table.

 

[smozgur]

Welcome to the MrExcel Message Board!

See if the following will give the idea. The Source is just a sample data in the code.
Otherwise, please try to add your sample source data, current query, and the desired result by using XL2BB.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjS1tDAyUNJRMjUEEoZKsTpIYiZmWMSwqDM2xRQzNDTGFDQ1ActYohkJ0m5sghAEW2GAqh0kBlZthCpmjKbOCGQgmjqQmClUXSwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [article = _t, qty = _t, Count = _t]),
    GroupRows = Table.Group(Source, {"article"}, {{"Table", each _}}),

    Result = Table.TransformColumns(GroupRows, 
                {
                    "Table", 
                    each Lines.ToText(
                            Table.CombineColumns(_, {"qty", "Count"}, Combiner.CombineTextByDelimiter(" x "), "temp")[temp])
                }
            )
in
    Result

This is the query/data structure:

and the result (you need to set cells back in Excel as Word Wrap).

 

[Watever]

Welcome to the MrExcel Message Board!

See if the following will give the idea. The Source is just a sample data in the code.
Otherwise, please try to add your sample source data, current query, and the desired result by using XL2BB.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjS1tDAyUNJRMjUEEoZKsTpIYiZmWMSwqDM2xRQzNDTGFDQ1ActYohkJ0m5sghAEW2GAqh0kBlZthCpmjKbOCGQgmjqQmClUXSwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [article = _t, qty = _t, Count = _t]),
    GroupRows = Table.Group(Source, {"article"}, {{"Table", each _}}),

    Result = Table.TransformColumns(GroupRows,
                {
                    "Table",
                    each Lines.ToText(
                            Table.CombineColumns(_, {"qty", "Count"}, Combiner.CombineTextByDelimiter(" x "), "temp")[temp])
                }
            )
in
    Result

This is the query/data structure:
View attachment 112416
and the result (you need to set cells back in Excel as Word Wrap).
View attachment 112417

This is perfect, it works wonderfuly. I didn't know about the "each lines to text" step.
I just had to convert my columns number to text in the other queries where I merged.
It didn't seem possible to do it inside the result step or in the merge.

But it works, thank you very much.

 

[smozgur]

You're welcome. Glad to hear it helps.

I think we can change the column types in the same query by using the Table.TransformColumnTypes function.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjS1tDAyUNJRMjUEEoZKsTpIYiZmWMSwqDM2xRQzNDTGFDQ1ActYohkJ0m5sghAEW2GAqh0kBlZthCpmjKbOCGQgmjqQmClUXSwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [article = _t, qty = _t, Count = _t]),
    PrepareData = Table.TransformColumnTypes(Source,{{"qty", Int64.Type}, {"Count", Int64.Type}}),
    GroupRows = Table.Group(PrepareData, {"article"}, {{"Table", each _}}),
    
    Result = Table.TransformColumns(GroupRows, 
                {
                    "Table", 
                    each Lines.ToText(
                            Table.CombineColumns(
                                Table.TransformColumnTypes(_,  {{"qty", type text}, {"Count", type text}}),
                                {"qty", "Count"},Combiner.CombineTextByDelimiter(" x "), "temp"
                            )[temp]   
                        )
                }
            )
in
    Result

 

[veyselemre]

Hello, how do you obtain this data?

i45WMjS1tDAyUNJRMjUEEoZKsTpIYiZmWMSwqDM2xRQzNDTGFDQ1ActYohkJ0m5sghAEW2GAqh0kBlZthCpmjKbOCGQgmjqQmClUXSwA

 

[smozgur]

Hello, how do you obtain this data?

Hello, @veyselemre

Home tab -> Click Enter Data:

Enter data manually, or copy & paste from a range:

Click OK, it will create a new query with data you just entered:

Go to the Source step and copy it to use in another query, or directly keep working on the same query.

It is just a base-64 encoded string - compressed version of the manually entered data table. Then Binary functions are used to decompress it to be used in the query.
This is what we generally use to provide sample data with Power Query implementations.

 

[veyselemre]

@smozgur ,

Thank you very much for your response.

 

[smozgur]

@smozgur ,

Thank you very much for your response.

You're welcome.