Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
mhbon
Frequent Visitor

Power query, merge column with itself?

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 ACompany A
Company ACompany B
Company ACompany C
Company BCompany A
Company BCompany B
Company BCompany C
Company CCompany A
........

 

Any nudge in the right direction would be greatly appreciated.

Thanks in advance!

2 ACCEPTED SOLUTIONS
mahoneypat
Employee
Employee

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

 





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


View solution in original post

edhans
Super User
Super User

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:

2020-05-10 12_50_07-Untitled - Power Query Editor.png

 

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"

 



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

View solution in original post

2 REPLIES 2
edhans
Super User
Super User

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:

2020-05-10 12_50_07-Untitled - Power Query Editor.png

 

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"

 



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
mahoneypat
Employee
Employee

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

 





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.