Forum Discussion
Split One column into multiple columns
HI, I am new to power bi and sql. Im working on power bi audit log report file. The file contains a column 'AuditDate' n it has multiple columns in it, I need to split that column into multiple col...
Hi Anonymous,
You can direct handle these text value in query edit.
Fully query:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bZFbb4IwFMe/iuFpD4OVq5e9zGlMyDI1gvNBfCjtUeuAklJndNl3H5aOuMSEl/L7X3p61mvjOzFCmhiDxPA8Co4X9BLjMTEWQLig8bmEmjmo/jMSgCXjRcxyUHoH2T0T1Z8b287ARQPXU9ZZCUIpleqDwWkBJReygWKHC3ZRXPd2u33fb1qXFQjdifTxDc5KNZlMhu54qGQrLj4zjhv7nJ9AvIatX6ce+L54wSmxCM8VG2UMChnOFe1bvuVaTtC6hrsaKvTOLyzLcJI8+RbqPKxYQfmp6kzjjo0s9KwcQyLZF5PnexOGEvIp1m8U4Qyq9s5RiQm0LAach/FM0TGWuAL532frTVyj75FrZHWN1CNDDzBGBDcPnR6ASE1uTLrpb+fEpyamnm9CGvhecNPYKlJk9vuYmhQ8O/XtABGv20xaRUdCoKoHHEhxBJ1ecp4tYCug2kdkD/SYgd5pYkx5Ae01rsKWLAsKW1ZA3fpjbDa/", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [AuditDate = _t]), #"Added Custom" = Table.AddColumn(Source, "Custom", each Json.Document([AuditDate])), #"Expanded Custom" = Table.ExpandRecordColumn(#"Added Custom", "Custom", {"Id", "RecordType", "CreationTime", "Operation", "OrganizationId", "UserType"}, {"Id", "RecordType", "CreationTime", "Operation", "OrganizationId", "UserType"}), #"Removed Columns" = Table.RemoveColumns(#"Expanded Custom",{"AuditDate"}) in #"Removed Columns"Regards,
Xiaoxin Sheng
Hi Anonymous,
You can direct handle these text value in query edit.
Fully query:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bZFbb4IwFMe/iuFpD4OVq5e9zGlMyDI1gvNBfCjtUeuAklJndNl3H5aOuMSEl/L7X3p61mvjOzFCmhiDxPA8Co4X9BLjMTEWQLig8bmEmjmo/jMSgCXjRcxyUHoH2T0T1Z8b287ARQPXU9ZZCUIpleqDwWkBJReygWKHC3ZRXPd2u33fb1qXFQjdifTxDc5KNZlMhu54qGQrLj4zjhv7nJ9AvIatX6ce+L54wSmxCM8VG2UMChnOFe1bvuVaTtC6hrsaKvTOLyzLcJI8+RbqPKxYQfmp6kzjjo0s9KwcQyLZF5PnexOGEvIp1m8U4Qyq9s5RiQm0LAach/FM0TGWuAL532frTVyj75FrZHWN1CNDDzBGBDcPnR6ASE1uTLrpb+fEpyamnm9CGvhecNPYKlJk9vuYmhQ8O/XtABGv20xaRUdCoKoHHEhxBJ1ecp4tYCug2kdkD/SYgd5pYkx5Ae01rsKWLAsKW1ZA3fpjbDa/", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [AuditDate = _t]),
#"Added Custom" = Table.AddColumn(Source, "Custom", each Json.Document([AuditDate])),
#"Expanded Custom" = Table.ExpandRecordColumn(#"Added Custom", "Custom", {"Id", "RecordType", "CreationTime", "Operation", "OrganizationId", "UserType"}, {"Id", "RecordType", "CreationTime", "Operation", "OrganizationId", "UserType"}),
#"Removed Columns" = Table.RemoveColumns(#"Expanded Custom",{"AuditDate"})
in
#"Removed Columns"
Regards,
Xiaoxin Sheng