Power query, merge column with itself?

[mhbon]

My title might be way of, but as a non-native speaker I just can't word my question any other way. If there is a term for this, please include it in your response.

So I have a column of companies:

Company

Company A

Company B

Company C

Is there a way in Power Query I could change it into something like this?

Company	Other Company
Company A	Company A
Company A	Company B
Company A	Company C
Company B	Company A
Company B	Company B
Company B	Company C
Company C	Company A
....	....

Any nudge in the right direction would be greatly appreciated.

Thanks in advance!

[mahoneypat · Accepted solution]

Yes.  As you suggest, you can merge a table with itself.  Please make a blank query, open the Advanced Editor and paste in this code in place of what is there, to see an example with Company A, B, C.

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wcs7PLUjMq1RwVIrVQfCcUHjOSrGxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Company = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Company", type text}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Source),
#"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Company"}, {"Company.1"}),
#"Renamed Columns" = Table.RenameColumns(#"Expanded Custom",{{"Company.1", "Other Company"}}),
#"Changed Type1" = Table.TransformColumnTypes(#"Renamed Columns",{{"Other Company", type text}})
in
#"Changed Type1"

If this solution works for you, please mark it as complete.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[edhans · Accepted solution]

Yes. This is a bit of a Cartesian Join.

Add a new column, then in the new column, the formula is simply the step name before it, so =#"Changed Type" for example, then expand.

It returns this:

See M code here:

1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wcs7PLUjMq1RwVIrVQfCcUHjOSrGxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Company = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Company", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each #"Changed Type"),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Company"}, {"Company.1"})
in
    #"Expanded Custom"

[mahoneypat · Accepted solution]

Yes.  As you suggest, you can merge a table with itself.  Please make a blank query, open the Advanced Editor and paste in this code in place of what is there, to see an example with Company A, B, C.

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wcs7PLUjMq1RwVIrVQfCcUHjOSrGxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Company = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Company", type text}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Source),
#"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Company"}, {"Company.1"}),
#"Renamed Columns" = Table.RenameColumns(#"Expanded Custom",{{"Company.1", "Other Company"}}),
#"Changed Type1" = Table.TransformColumnTypes(#"Renamed Columns",{{"Other Company", type text}})
in
#"Changed Type1"

If this solution works for you, please mark it as complete.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat