split columns with with multiple spaces between fields

[Anonymous]

Hey Team,

We need to split text which looks like this to columns:

Name= John Doe Date=2021-2-3 Address= New York

Name= Sam Doe Date=2021-2-3 Address= City= Paris

Is there a way to parse this to different columns?

Some fields are blank from the log files.

[v-stephen-msft]

Hi @Anonymous ,

Please check the attachment.

The workaround is to Split columns by delimiter.

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.

[watkinnc]

it was actually trickier than it seemed! Here you are.

Spilt those fields! 

--Nate

[Anonymous]

Im not able to download this @watkinnc 

[Jakinta]

This also can be done.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8kvMTbVV8MrPyFNwyU9VcEksSbU1MjAy1DXSNVZwTEkpSi0utlXwSy1XiMwvylaK1YFpCU7MxafDObOk0lYhILEos1gpNhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Split Column by Delimiter" = Table.SplitColumn(Source, "Column1", Splitter.SplitTextByAnyDelimiter({"Name= "," Date=", " Address= "}, QuoteStyle.Csv), {"Column1","Name", "Date", "City"}),
    #"Removed Columns" = Table.RemoveColumns(#"Split Column by Delimiter",{"Column1"}),
    #"Replaced Value" = Table.ReplaceValue(#"Removed Columns","City= ","",Replacer.ReplaceText,{"City"})
in
    #"Replaced Value"

[Anonymous]

It has variable column names. 

[watkinnc]

I would split by delimiter "=", then Text.Trim the whole table to remove any leading or trailing spaces. I would then add an index column and move it all the way left. Then you should be able to use the Pivot function in the GUI to pivot the columns. 
--Nate

[Anonymous]

Can you help me with a sample pbix file?

[watkinnc]

I'm on my phone on the road. Give me an hour or so  🙂

--Nate

[Anonymous]

@watkinnc were you able to generate a sample pbix?

[Anonymous]

Thank you!