Join us at FabCon Atlanta from March 16 - 20, 2026, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.
Register now!To celebrate FabCon Vienna, we are offering 50% off select exams. Ends October 3rd. Request your discount now.
Solved! Go to Solution.
Hi @Imo22 ,
This video inspired me.
Single column to multiple columns in excel | Power Bi | Power Query - YouTube
Sample data:
Expected results:
All operations are in the Power Query Editor.
Download my attachment and you will see the specific steps in PQ.
M code:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WckksSVWK1YlWMjIwMtI10TU0QuEZo/BMwLzg/JwUMMMQIgkmIQohCgKK8lNKk0sUPF0gygwMDGEMIxjDGMYAaokFAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
#"Reversed Rows" = Table.ReverseRows(#"Changed Type"),
#"Reversed Rows1" = Table.ReverseRows(#"Reversed Rows"),
#"Duplicated Column" = Table.DuplicateColumn(#"Reversed Rows1", "Column1", "Column1 - Copy"),
#"Changed Type1" = Table.TransformColumnTypes(#"Duplicated Column",{{"Column1 - Copy", Int64.Type}}),
#"Added Conditional Column" = Table.AddColumn(#"Changed Type1", "Custom", each if Text.Contains([Column1], "-") then [Column1] else null),
#"Replaced Errors" = Table.ReplaceErrorValues(#"Added Conditional Column", {{"Column1 - Copy", null}}),
#"Added Conditional Column1" = Table.AddColumn(#"Replaced Errors", "Custom.1", each if [#"Column1 - Copy"] = null and [Custom] = null then [Column1] else null),
#"Filled Down" = Table.FillDown(#"Added Conditional Column1",{"Custom.1"}),
#"Removed Columns" = Table.RemoveColumns(#"Filled Down",{"Column1 - Copy", "Custom"}),
#"Grouped Rows" = Table.Group(#"Removed Columns", {"Custom.1"}, {{"Count", each _, type table [Column1=nullable text, Custom.1=text]}}),
#"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([Count],"Index",1,1)),
#"Removed Columns1" = Table.RemoveColumns(#"Added Custom",{"Count"}),
#"Expanded Custom" = Table.ExpandTableColumn(#"Removed Columns1", "Custom", {"Column1", "Index"}, {"Column1", "Index"}),
#"Pivoted Column" = Table.Pivot(#"Expanded Custom", List.Distinct(#"Expanded Custom"[Custom.1]), "Custom.1", "Column1"),
#"Promoted Headers" = Table.PromoteHeaders(#"Pivoted Column", [PromoteAllScalars=true]),
#"Changed Type2" = Table.TransformColumnTypes(#"Promoted Headers",{{"1", Int64.Type}, {"Date", type date}, {"Sold", Int64.Type}, {"Product ID", Int64.Type}})
in
#"Changed Type2"
Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Imo22 ,
This video inspired me.
Single column to multiple columns in excel | Power Bi | Power Query - YouTube
Sample data:
Expected results:
All operations are in the Power Query Editor.
Download my attachment and you will see the specific steps in PQ.
M code:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WckksSVWK1YlWMjIwMtI10TU0QuEZo/BMwLzg/JwUMMMQIgkmIQohCgKK8lNKk0sUPF0gygwMDGEMIxjDGMYAaokFAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
#"Reversed Rows" = Table.ReverseRows(#"Changed Type"),
#"Reversed Rows1" = Table.ReverseRows(#"Reversed Rows"),
#"Duplicated Column" = Table.DuplicateColumn(#"Reversed Rows1", "Column1", "Column1 - Copy"),
#"Changed Type1" = Table.TransformColumnTypes(#"Duplicated Column",{{"Column1 - Copy", Int64.Type}}),
#"Added Conditional Column" = Table.AddColumn(#"Changed Type1", "Custom", each if Text.Contains([Column1], "-") then [Column1] else null),
#"Replaced Errors" = Table.ReplaceErrorValues(#"Added Conditional Column", {{"Column1 - Copy", null}}),
#"Added Conditional Column1" = Table.AddColumn(#"Replaced Errors", "Custom.1", each if [#"Column1 - Copy"] = null and [Custom] = null then [Column1] else null),
#"Filled Down" = Table.FillDown(#"Added Conditional Column1",{"Custom.1"}),
#"Removed Columns" = Table.RemoveColumns(#"Filled Down",{"Column1 - Copy", "Custom"}),
#"Grouped Rows" = Table.Group(#"Removed Columns", {"Custom.1"}, {{"Count", each _, type table [Column1=nullable text, Custom.1=text]}}),
#"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([Count],"Index",1,1)),
#"Removed Columns1" = Table.RemoveColumns(#"Added Custom",{"Count"}),
#"Expanded Custom" = Table.ExpandTableColumn(#"Removed Columns1", "Custom", {"Column1", "Index"}, {"Column1", "Index"}),
#"Pivoted Column" = Table.Pivot(#"Expanded Custom", List.Distinct(#"Expanded Custom"[Custom.1]), "Custom.1", "Column1"),
#"Promoted Headers" = Table.PromoteHeaders(#"Pivoted Column", [PromoteAllScalars=true]),
#"Changed Type2" = Table.TransformColumnTypes(#"Promoted Headers",{{"1", Int64.Type}, {"Date", type date}, {"Sold", Int64.Type}, {"Product ID", Int64.Type}})
in
#"Changed Type2"
Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hello experts , as you can see I get the data , products name , Date of selling and time in one column .... how can I get all different values saparately into differnt columns ,, Product , Date of selling and Time in power Bi .
best regards