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.
I have a custom field within my Table that has multiple heading and results
ContactTime: "Date",
ConractSent: "Date",
ReceivedTime: "Date",
This is all in one field and I am able to split the data but I want to create the Heading into column headings with the results in the respective rows below
Is that possible?
Solved! Go to Solution.
Hi @SamBrown17, is this the result you are looking for?
steps in PQ:
M-Code:
let
Source = Csv.Document(File.Contents("C:\Users\Admin\Desktop\Sam Brown17\SamBrown17.csv"),[Delimiter=",", Columns=1, Encoding=1252]),
#"Split into rows at each comma" = Table.ExpandListColumn(Table.TransformColumns(Source, {{"Column1", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Column1"),
#"Replaced { with blank" = Table.ReplaceValue(#"Split into rows at each comma","{","",Replacer.ReplaceText,{"Column1"}),
#"Replaced } with blank" = Table.ReplaceValue(#"Replaced { with blank","}","",Replacer.ReplaceText,{"Column1"}),
#"Trim text to remove extra spaces" = Table.TransformColumns(#"Replaced } with blank",{{"Column1", Text.Trim, type text}}),
#"Split at leftmost colon" = Table.SplitColumn(#"Trim text to remove extra spaces", "Column1", Splitter.SplitTextByEachDelimiter({":"}, QuoteStyle.Csv, false), {"Column1.1", "Column1.2"}),
#"Split at rightmost period" = Table.SplitColumn(#"Split at leftmost colon", "Column1.2", Splitter.SplitTextByEachDelimiter({"."}, QuoteStyle.Csv, true), {"Column1.2.1", "Column1.2.2"}),
#"Removed Columns" = Table.RemoveColumns(#"Split at rightmost period",{"Column1.2.2"}),
#"Transpose Table" = Table.Transpose(#"Removed Columns"),
#"Use 1st row as headers" = Table.PromoteHeaders(#"Transpose Table", [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Use 1st row as headers",{{"assessmentContactTime", type datetime}, {"assessmentRequestDate", type datetime}, {"assessment_bookedDate", type datetime}, {"assessmentAttendanceDueDate", type datetime}})
in
#"Changed Type"
Here's a quick GIF showing the PQ steps:
@SamBrown17 , I think part of the question is missing. Also not very clear. Can share a screenshot too
Sorry first time posting
I have the above in my fields within a table, and I want to be able to isolate the text prior to ":" and have that as a heading of a column in a new table, and then the text after the ":" as the row results and then have that link to the original table so I can look at a job within a master table and see when the Assessment was Booked?
So It starts out like this
I then split it,
But then want to create a new table so that I have the results of each milestone.
Hi @SamBrown17, is this the result you are looking for?
steps in PQ:
M-Code:
let
Source = Csv.Document(File.Contents("C:\Users\Admin\Desktop\Sam Brown17\SamBrown17.csv"),[Delimiter=",", Columns=1, Encoding=1252]),
#"Split into rows at each comma" = Table.ExpandListColumn(Table.TransformColumns(Source, {{"Column1", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Column1"),
#"Replaced { with blank" = Table.ReplaceValue(#"Split into rows at each comma","{","",Replacer.ReplaceText,{"Column1"}),
#"Replaced } with blank" = Table.ReplaceValue(#"Replaced { with blank","}","",Replacer.ReplaceText,{"Column1"}),
#"Trim text to remove extra spaces" = Table.TransformColumns(#"Replaced } with blank",{{"Column1", Text.Trim, type text}}),
#"Split at leftmost colon" = Table.SplitColumn(#"Trim text to remove extra spaces", "Column1", Splitter.SplitTextByEachDelimiter({":"}, QuoteStyle.Csv, false), {"Column1.1", "Column1.2"}),
#"Split at rightmost period" = Table.SplitColumn(#"Split at leftmost colon", "Column1.2", Splitter.SplitTextByEachDelimiter({"."}, QuoteStyle.Csv, true), {"Column1.2.1", "Column1.2.2"}),
#"Removed Columns" = Table.RemoveColumns(#"Split at rightmost period",{"Column1.2.2"}),
#"Transpose Table" = Table.Transpose(#"Removed Columns"),
#"Use 1st row as headers" = Table.PromoteHeaders(#"Transpose Table", [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Use 1st row as headers",{{"assessmentContactTime", type datetime}, {"assessmentRequestDate", type datetime}, {"assessment_bookedDate", type datetime}, {"assessmentAttendanceDueDate", type datetime}})
in
#"Changed Type"
Is it possible to get a more detailed PQ step please, For the ability for me to fast repeat?
You did it, thank you Worked well
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.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
93 | |
84 | |
66 |