Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Below I have a dataset where in the left column, "Operation Sale Types" I have various sales types that are contained within pipes. If you look at line 9, you can see that there are 2 different operation sale types. In the right column, "Operation Line Cost", is the associated value for each type also contained within pipes. I want to be able to associate value 1 within column A to value 1 within column B, and so on. Any recommendations?
Solved! Go to Solution.
Hi @leibowjb,
You can refer to below steps to expand those mixed columns.
Steps:
1. Enter to query editor.
2. Add custom column to transfer these column to table.
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Table.FromColumns({Text.Split([Type],"|"),Text.Split([Cost],"|")},{"Type","Cost"}))
3. Remove origianl type, cost columns.
#"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"ID", "Custom"})
4. Expand table to new row.
Full query:
let Source = Excel.Workbook(File.Contents("C:\Users\xxxx\Desktop\sample.xlsx"), null, true), #"Merge Records_Sheet" = Source{[Item="Merge Records",Kind="Sheet"]}[Data], #"Promoted Headers" = Table.PromoteHeaders(#"Merge Records_Sheet", [PromoteAllScalars=true]), #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"ID", Int64.Type}, {"Type", type text}, {"Cost", type text}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Table.FromColumns({Text.Split([Type],"|"),Text.Split([Cost],"|")},{"Type","Cost"})), #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"ID", "Custom"}), #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Other Columns", "Custom", {"Type", "Cost"}, {"Type", "Cost"}) in #"Expanded Custom"
Regards,
Xiaoxin Sheng
Hi @leibowjb,
You can refer to below steps to expand those mixed columns.
Steps:
1. Enter to query editor.
2. Add custom column to transfer these column to table.
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Table.FromColumns({Text.Split([Type],"|"),Text.Split([Cost],"|")},{"Type","Cost"}))
3. Remove origianl type, cost columns.
#"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"ID", "Custom"})
4. Expand table to new row.
Full query:
let Source = Excel.Workbook(File.Contents("C:\Users\xxxx\Desktop\sample.xlsx"), null, true), #"Merge Records_Sheet" = Source{[Item="Merge Records",Kind="Sheet"]}[Data], #"Promoted Headers" = Table.PromoteHeaders(#"Merge Records_Sheet", [PromoteAllScalars=true]), #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"ID", Int64.Type}, {"Type", type text}, {"Cost", type text}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Table.FromColumns({Text.Split([Type],"|"),Text.Split([Cost],"|")},{"Type","Cost"})), #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"ID", "Custom"}), #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Other Columns", "Custom", {"Type", "Cost"}, {"Type", "Cost"}) in #"Expanded Custom"
Regards,
Xiaoxin Sheng
Seems like you are going to have to do a split on those columns based on your pipe character and then do some sort of pivot or something. Can you post your sample data as something that can be copied easily?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
100 | |
76 | |
74 | |
49 |
User | Count |
---|---|
146 | |
108 | |
106 | |
90 | |
62 |