Forum Discussion

joseluissa001's avatar
joseluissa001
Frequent Visitor
2 years ago
Solved

Range to columns

Hello, good afternoon community, I have a question about how to solve a problem that I have encountered.
I have a file that is filled out with a Microsoft form, these are vehicle inspection procedures for a company but the procedures do not appear in the correct form: I need to get from example 1 to 2. I will be very grateful for your help.

 

example 1 
Clientregistration
BobBPA2222, PMA7777, PLA8888
JoshMAD9878, LDC5475, AAD7369

 

example 1 
Clientregistration
BobBPA2222
BobPMA7777
BobPLA8888
JoshMAD9878
JoshLDC5475
JoshAAD7369
Need Help
  • Syndicate_Admin's avatar
    Syndicate_Admin
    2 years ago
    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcspPUtJRcgpwNAICHYUAX0dzIAAyfBwtgEApVidaySu/OAOoyNfRxdLC3EJHwcfF2dTE3FRHwdHRxdzYzFIpNhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Client = _t, registration = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Client", type text}, {"registration", type text}}),
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Changed Type", {{"registration", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "registration"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"registration", type text}})
in
    #"Changed Type1"

2 Replies

  • Syndicate_Admin's avatar
    Syndicate_Admin
    Administrator
    2 years ago
    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcspPUtJRcgpwNAICHYUAX0dzIAAyfBwtgEApVidaySu/OAOoyNfRxdLC3EJHwcfF2dTE3FRHwdHRxdzYzFIpNhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Client = _t, registration = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Client", type text}, {"registration", type text}}),
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Changed Type", {{"registration", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "registration"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"registration", type text}})
in
    #"Changed Type1"