Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

One Column but different data grouped

Good Morning,    I am working with data in excel and it comes in seperated by lines but when imported into excel it is one main column and reads the  Region / Area / store number  all as the same ...
Need Help
  • v-frfei-msft's avatar
    v-frfei-msft
    7 years ago

    Hi Anonymous ,

     

    We can split column in power query like this.

     

    M code for your reference:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCkpNz8zPU9BXcCxKTQRSxSX5RakKeaW5SalFSrGxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "Column1", Splitter.SplitTextByDelimiter("/", QuoteStyle.Csv), {"Column1.1", "Column1.2", "Column1.3"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Column1.1", type text}, {"Column1.2", type text}, {"Column1.3", type text}})
in
    #"Changed Type1"
Nathaniel_C's avatar
Nathaniel_C
Icon for Community Champion rankCommunity Champion
7 years ago

Hi Anonymous ,

Are you working in PowerQuery? Then Split Column at delimiter. In Excel, go to Data tab, Select Text to Columns, use custom delimiter.
Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel