Combining Fields with PowerQuery

[DavidG007]

Hi All,

Just hoping someone maybe able to help, what I am trying to do is;

I currently have two Queries; I need help to be able to 'fix' the data in Query1 and concatenate the data in Query2 as per below.

Query 1	Query 2	NEW QUERY (Derived)
a	1	a1
b	2	a2
	3	a3
	4	a4
	5	a5
		b1
		b2

etc etc

Can anyone help?

Thanks for anything

 

[sandy666]

like this?

// Query1
let
    Source = {"a","b"},
    C2T = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
in
    C2T

// Query2
let
    Source = {1..5},
    C2T = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
in
    C2T

// Query3
let
    Source = Query1,
    P2P = Table.AddColumn(Source, "Custom", each Query2),
    Expand = Table.ExpandTableColumn(P2P, "Custom", {"Column1"}, {"Column1.1"}),
    Merge = Table.CombineColumns(Table.TransformColumnTypes(Expand, {{"Column1.1", type text}}, "en-GB"),{"Column1", "Column1.1"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Merged")
in
    Merge

 

[DavidG007]

Hi Sandy,

A huge thank you for taking a look at my query, very much appreciated.

Yes, your extract is spot on. I will just see if I can apply your formula,

thanks again

 

[sandy666]

Be aware that all queries are created in Power Query Editor

 

[DavidG007]

Hi Sandy,

I've been trying to follow your code above but I'm struggling to get it to work.... I don't suppose you could ping over the simple spreadsheet you may have done to test so that I can try and follow?

Thanks again

 

[sandy666]

TinyUpload.com - best file hosting solution, with no limits, totaly free

TinyUpload.com - solution for tiny file hosting. No download limits, no upload limit. Totaly free.

s000.tinyupload.com

 

[anvg]

Hi
Single query for advanced editor

let
    letters = Table.FromColumns({{"a".."b"}}, {"letter"}),
    numberTexts = Table.FromColumns({{1..5}}, {"number"}),
    cartesian = Table.Join(letters, {}, numberTexts, {}),
    combine = Table.AddColumn(cartesian, "combo", each [letter] & Text.From([number]))[[combo]]
in
    combine

Regards,

 

[DavidG007]

Thanks anvg for your reply, I am still seeing an error though when I try and follow the steps suggested, Sandy, are you able to send over the sample file so that hopefully it shows me where I am going wrong?

Many thanks

 

[DavidG007]

Hi both..... the eagle has finally landed! all working now, thanks once again, really appreciated

 

[sandy666]

post#6 - there was example file
post#9 - you are welcome