Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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!
Solved! Go to 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
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
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"
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingYes. 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"
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingYes. 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
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
User | Count |
---|---|
141 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
123 | |
101 | |
71 | |
61 |