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

[Imo22]

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 

[Anonymous · Accepted 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.

[Imo22]

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