Forum Discussion
Adding New column in advanced editor not pulling through
To give context of what I am trying to do. I have 3 colums in my table which I am wanting to cleanse by stripping out everything but numbers and . (in essense there is a freetext field which currency...
Hi Anonymous,
Try this code. I replaced "[All Chars]"s with the relevant column names.let Source = SharePoint.Tables("a sharepointlist", [Implementation="2.0", ViewMode="All"]), #"b0c46bfb-56a4-4ca3-923d-6f0c9a6948f2" = Source{[Id="b0c46bfb-56a4-4ca3-923d-6f0c9a6948f2"]}[Items], #"Type modifié" = Table.TransformColumnTypes(#"b0c46bfb-56a4-4ca3-923d-6f0c9a6948f2",{{"ContractReview", type date}, {"MeasureStartProcessDate", type date}, {"PreMeasureStartDateChecksT-2", type date}, {"MeasureDateCompletion", type date}, {"MeasureCompletion", type date}, {"OrderPlacement", type date}, {"ConfirmationFromSupplier", type date}, {"DeliveryDateExpectedSuppliedToClient", type date}, {"ConfirmationToClientOfGarmentReceipt", type date}, {"PreInstallMeeting", type date}, {"InstallationDate", type date}, {"Created", type date}}), #"Expanded Modified By" = Table.ExpandListColumn(#"Type modifié", "Modified By"), #"Expanded Modified By1" = Table.ExpandRecordColumn(#"Expanded Modified By", "Modified By", {"id", "title", "email", "sip", "picture"}, {"Modified By.id", "Modified By.title", "Modified By.email", "Modified By.sip", "Modified By.picture"}), #"Expanded Created By" = Table.ExpandListColumn(#"Expanded Modified By1", "Created By"), #"Expanded Created By1" = Table.ExpandRecordColumn(#"Expanded Created By", "Created By", {"title"}, {"Created By.title"}), CleansedOldMIV = Table.AddColumn(#"Expanded Created By1", "CleansedOldMIV", each Text.Select([OldMIV], {Character.FromNumber(46),Character.FromNumber(48)..Character.FromNumber(57)})), CleansedPredictedMIV = Table.AddColumn(CleansedOldMIV, "CleansedPredictedMIV", each Text.Select([PredictedMIV], {Character.FromNumber(46),Character.FromNumber(48)..Character.FromNumber(57)})), CleansedActualMIV = Table.AddColumn(CleansedPredictedMIV, "CleansedActualMIV", each Text.Select([ActualMIV], {Character.FromNumber(46),Character.FromNumber(48)..Character.FromNumber(57)})) in CleansedActualMIV
If this doesn't work, please send a screen capture of the table structure before adding the 3 "cleaned" columns.
You can use: Table.Schema(#"Expanded Created By") to generate the schema.
Hi Anonymous,
I've fixed those parts:
Try this code
let
Source = SharePoint.Tables("a sharepointlist", [Implementation="2.0", ViewMode="All"]),
#"b0c46bfb-56a4-4ca3-923d-6f0c9a6948f2" = Source{[Id="b0c46bfb-56a4-4ca3-923d-6f0c9a6948f2"]}[Items],
#"Type modifié" = Table.TransformColumnTypes(#"b0c46bfb-56a4-4ca3-923d-6f0c9a6948f2",{{"ContractReview", type date}, {"MeasureStartProcessDate", type date}, {"PreMeasureStartDateChecksT-2", type date}, {"MeasureDateCompletion", type date}, {"MeasureCompletion", type date}, {"OrderPlacement", type date}, {"ConfirmationFromSupplier", type date}, {"DeliveryDateExpectedSuppliedToClient", type date}, {"ConfirmationToClientOfGarmentReceipt", type date}, {"PreInstallMeeting", type date}, {"InstallationDate", type date}, {"Created", type date}}),
#"Expanded Modified By" = Table.ExpandListColumn(#"Type modifié", "Modified By"),
#"Expanded Modified By1" = Table.ExpandRecordColumn(#"Expanded Modified By", "Modified By", {"id", "title", "email", "sip", "picture"}, {"Modified By.id", "Modified By.title", "Modified By.email", "Modified By.sip", "Modified By.picture"}),
#"Expanded Created By" = Table.ExpandListColumn(#"Expanded Modified By1", "Created By"),
#"Expanded Created By1" = Table.ExpandRecordColumn(#"Expanded Created By", "Created By", {"title"}, {"Created By.title"}),
CleansedOldMIV = Table.AddColumn(#"Expanded Created By1", "CleansedOldMIV", each Text.Select([All Chars], {Character.FromNumber(46),Character.FromNumber(48)..Character.FromNumber(57)})),
CleansedPredictedMIV = Table.AddColumn(CleansedOldMIV, "CleansedPredictedMIV", each Text.Select([All Chars], {Character.FromNumber(46),Character.FromNumber(48)..Character.FromNumber(57)})),
CleansedActualMIV = Table.AddColumn(CleansedPredictedMIV, "CleansedActualMIV", each Text.Select([All Chars], {Character.FromNumber(46),Character.FromNumber(48)..Character.FromNumber(57)}))
in
CleansedActualMIV
Thanks for this its pulling through the colums but not quite as I expected. (they are all blank)
The coloums are each seperate and should be pulling out the values from, OldMIV, PredictedMIV, ActualMIV