Forum Discussion

pharcoff's avatar
pharcoff
New Member
6 years ago
Solved

How to extract data into multiple columns from a single column of varying fields in data source?

I've got a data source table extracted from Workday in an Excel file. I want to be able to import it into Power BI, however one of the columns in the data source contains multiple types of data that ...
Need Help
  • v-easonf-msft's avatar
    v-easonf-msft
    6 years ago

    Hi ,  pharcoff 

     

    You can  try follow step:

    1.create custom column

     

     

    =let 
    t = Table.SelectRows(Table.FromList(Lines.FromText([Column1]), Splitter.SplitByNothing(), null, null, ExtraValues.Error),each [Column1] <> ""),
    t2 = Table.TransformColumnTypes(Table.SplitColumn(t, "Column1", Splitter.SplitTextByDelimiter(":", QuoteStyle.Csv), {"Column1.1", "Column1.2"}),{{"Column1.1", type text}, {"Column1.2", type text}})
    in Table.PromoteHeaders(Table.Transpose(t2), [PromoteAllScalars=true])

     

     

     

     

     

    2.expanded  custom column

     

    Here is a sample i made:

    url: 

    https://wicren-my.sharepoint.com/:u:/g/personal/michael_wicren_onmicrosoft_com/ES9xAySGoVtEklCnkJWc_RwBhiW6F_TkeE6flFhm-KG23g?e=oo0KjK 

     

    Best Regards,
    Community Support Team _ Eason
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

v-easonf-msft's avatar
v-easonf-msft
Community Support
6 years ago

Hi ,  pharcoff 

 

You can  try follow step:

1.create custom column

 

 

=let 
    t = Table.SelectRows(Table.FromList(Lines.FromText([Column1]), Splitter.SplitByNothing(), null, null, ExtraValues.Error),each [Column1] <> ""),
    t2 = Table.TransformColumnTypes(Table.SplitColumn(t, "Column1", Splitter.SplitTextByDelimiter(":", QuoteStyle.Csv), {"Column1.1", "Column1.2"}),{{"Column1.1", type text}, {"Column1.2", type text}})
    in Table.PromoteHeaders(Table.Transpose(t2), [PromoteAllScalars=true])

 

 

 

 

 

2.expanded  custom column

 

Here is a sample i made:

url: 

https://wicren-my.sharepoint.com/:u:/g/personal/michael_wicren_onmicrosoft_com/ES9xAySGoVtEklCnkJWc_RwBhiW6F_TkeE6flFhm-KG23g?e=oo0KjK 

 

Best Regards,
Community Support Team _ Eason
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.