Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
I would like to split column by delimiter "," for below many rows, here two rows as example. the output should be in correct order for each values based on the new column name. for instance Department's values should go into column "Department" and Application's values should got into column"Application and etc..
Row No.1:
Application: Value1, Request: Value2, Department: Value3, PatchingGroup: Value4
Row No.2:
Department: Value1, Application: Value2, Request: Value3
I adapted the svcript of an older similar subject
let
Source = Table.FromRecords(Json.Document(Binary.Decompress(Binary.FromText("i65Wcs7PKc3NM1SyUnIsKMjJTE4syczPs1IIS8wpTTXUUQhKLSxNLS6BChjpKLikFiQWleSm5sHEjHUUAhJLkjMy89Ldi/JLC6DCJkq1OsimY+gDGo5poxG6jcZKtbEA",BinaryEncoding.Base64),Compression.Deflate))),
ac = Table.AddColumn(Source, "Custom",
each Record.FromList(
List.Transform(List.Alternate(Text.SplitAny(_[Column1],":,"),1,1,0),Text.TrimStart),
List.Transform(List.Alternate(Text.SplitAny(_[Column1],":,"),1,1,1),Text.TrimStart)
)
),
fields=List.Union(List.Transform(ac[Column1], each List.Transform(List.Alternate(Text.SplitAny(_,":,"),1,1,1),Text.TrimStart)))
in
Table.FromRecords(ac[Custom],fields,MissingField.UseNull)
written in a perhaps more orderly manner:
let
Source = Table.FromRecords(Json.Document(Binary.Decompress(Binary.FromText("i65Wcs7PKc3NM1SyUnIsKMjJTE4syczPs1IIS8wpTTXUUQhKLSxNLS6BChjpKLikFiQWleSm5sHEjHUUAhJLkjMy89Ldi/JLC6DCJkq1OsimY+gDGo5poxG6jcZKtbEA",BinaryEncoding.Base64),Compression.Deflate))),
selFieldOrValues = (string, fOv) => List.Transform(
List.Alternate(Text.SplitAny(string, ":,"), 1, 1, fOv),
Text.TrimStart
),
ac = Table.AddColumn(
Source,
"Custom",
each Record.FromList(selFieldOrValues([Column1], 0), selFieldOrValues([Column1], 1))
),
fields = List.Union(List.Transform(ac[Column1], each selFieldOrValues(_, 1)))
in
Table.FromRecords(ac[Custom], fields, MissingField.UseNull)
@Anonymous ,thank you very much, i will try and let you know in case i need further help.
let
Source = Table.FromRecords(Json.Document(Binary.Decompress(Binary.FromText("i65Wcs7PKc3NM1SyUnIsKMjJTE4syczPs1IIS8wpTTXUUQhKLSxNLS6BChjpKLikFiQWleSm5sHEjHUUAhJLkjMy89Ldi/JLC6DCJkq1OsimY+gDGo5poxG6jcZKtbEA",BinaryEncoding.Base64),Compression.Deflate))),
fx = (str)=> Expression.Evaluate("["&Text.Replace(Text.Replace(str,": ","="""),",",""",")&"""]"),
toList = List.Buffer(Table.ToList(Source, each _{0})),
acc = List.Accumulate(toList, {{},[]}, (s,c)=>let rec=fx(c) in {s{0}&{rec}, s{1}&rec}),
result = Table.FromRecords(acc{0}, Record.FieldNames(acc{1}), MissingField.UseNull)
in
result
let
Source = Table.FromRecords(Json.Document(Binary.Decompress(Binary.FromText("i65Wcs7PKc3NM1SyUnIsKMjJTE4syczPs1IIS8wpTTXUUQhKLSxNLS6BChjpKLikFiQWleSm5sHEjHUUAhJLkjMy89Ldi/JLC6DCJkq1OsimY+gDGo5poxG6jcZKtbEA",BinaryEncoding.Base64),Compression.Deflate))),
fx = (str)=> Expression.Evaluate("["&Text.Replace(Text.Replace(str,": ","="""),",",""",")&"""]"),
toRecs = Table.ToList(Source, each fx(_{0})),
result = Table.FromRecords(toRecs, Record.FieldNames(Record.Combine(toRecs)), 2)
in
result
I provide another method, this method is just learned from a group of PQ bigwigs, it can handle tens of thousands of rows of data with a high degree of efficiency, the previous method I provided can only handle about 2000 rows of data volume
@ziying35 , thank you very much, i will try and let you know in case i need further help.
Hi @mabdelrahman76 - I did this 100% through the UI with the code below.
Turns this:
into this:
Steps are:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WciwoyMlMTizJzM+zUghLzClNNdRRCEotLE0tLoEKGOkouKQWJBaV5KbmwcSMdRQCEkuSMzLz0t2L8ksLoMImSrE60UoYqoFGYtpjhG6PsVJsLAA=", 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.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Column1.1", "Column1.2", "Column1.3", "Column1.4"}),
#"Added Index" = Table.AddIndexColumn(#"Split Column by Delimiter", "Index", 0, 1, Int64.Type),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Added Index", {"Index"}, "Attribute", "Value"),
#"Trimmed Text" = Table.TransformColumns(#"Unpivoted Other Columns",{{"Value", Text.Trim, type text}}),
#"Split Column by Delimiter1" = Table.SplitColumn(#"Trimmed Text", "Value", Splitter.SplitTextByDelimiter(":", QuoteStyle.Csv), {"Value.1", "Value.2"}),
#"Removed Columns" = Table.RemoveColumns(#"Split Column by Delimiter1",{"Attribute"}),
#"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[Value.1]), "Value.1", "Value.2"),
#"Removed Columns1" = Table.RemoveColumns(#"Pivoted Column",{"Index"})
in
#"Removed Columns1"
How to use M code provided in a blank query:
1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done
5) See this article if you need help using this M code in your model.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingHi, @mabdelrahman76
This requirement is not difficult, I'll write a code for you in the morning
@mabdelrahman76 - Yeesh! Maybe @ImkeF or @edhans
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.