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

To celebrate FabCon Vienna, we are offering 50% off select exams. Ends October 3rd. Request your discount now.

Reply
Imo22
Frequent Visitor

POwer BI , how to take values from one column and put them in differnt columns or tables

Spoiler
Spoiler
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 

1.JPG

1 ACCEPTED SOLUTION
Anonymous
Not applicable

Hi @Imo22 ,

 

This video inspired me.

Single column to multiple columns in excel | Power Bi | Power Query - YouTube

 

Sample data:

vstephenmsft_1-1651114052894.png

Expected results:

vstephenmsft_2-1651114064332.png

 

All operations are in the Power Query Editor.

Download my attachment and you will see the specific steps in PQ.

vstephenmsft_3-1651114098957.png

 

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.

View solution in original post

2 REPLIES 2
Anonymous
Not applicable

Hi @Imo22 ,

 

This video inspired me.

Single column to multiple columns in excel | Power Bi | Power Query - YouTube

 

Sample data:

vstephenmsft_1-1651114052894.png

Expected results:

vstephenmsft_2-1651114064332.png

 

All operations are in the Power Query Editor.

Download my attachment and you will see the specific steps in PQ.

vstephenmsft_3-1651114098957.png

 

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.

Imo22
Frequent Visitor

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 

Helpful resources

Announcements
September Power BI Update Carousel

Power BI Monthly Update - September 2025

Check out the September 2025 Power BI update to learn about new features.

August 2025 community update carousel

Fabric Community Update - August 2025

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

Top Solution Authors
Top Kudoed Authors