Adding New column in advanced editor not pulling through

[Anonymous]

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 values are added and while the solution would be to fix the source its not possible to do so at this point)

so the three colums are OldMIV, PredictedMIV,ActualMIV  below i belive should work, however although i get no errors when i run this none of the new colums appear either in the editor or in the table on my powerbi table. Just wondering what I have done wrong here?

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("OldMIV", "CleansedOldMIV", each Text.Select([All Chars], {Character.FromNumber(46),Character.FromNumber(48)..Character.FromNumber(57)})),
CleansedPredictedMIV = Table.AddColumn("PredictedMIV", "CleansedPredictedMIV", each Text.Select([All Chars], {Character.FromNumber(46),Character.FromNumber(48)..Character.FromNumber(57)})),
CleansedActualMIV = Table.AddColumn("ActualMIV", "CleansedActualMIV", each Text.Select([All Chars], {Character.FromNumber(46),Character.FromNumber(48)..Character.FromNumber(57)}))

in
#"Expanded Created By1"

[SpartaBI · Accepted solution]

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.

Showcase Report – Contoso By SpartaBI

      

[Anonymous]

Thanks this pulls it through correctly - I was still trying to refer to a table I wasnt using anymore.

I now have another issue (not to do with this but the purpose of doing this was to clean out a load of rubbish to allow me to convert it to a deceimal/currency field, but it errors out when I do this - so I guess a bit more work for me to take a look at and solve)

[SpartaBI]

@Anonymous my pleasure 🙂
Hey, check out my showcase report - got some high level stuff there 🙂
https://community.powerbi.com/t5/Data-Stories-Gallery/SpartaBI-Feat-Contoso-100K/td-p/2449543
Give it a thumbs up over there if you liked it 🙂

[Anonymous]

will do thankyou 🙂 I found the cause of the conversion issues - people in the freetext field when adding numbers have devided they want to input figures with two decimal points like this 1.000.10 rather than using nothing or commas. Joy

[SpartaBI]

Great! 😀

[SpartaBI]

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

Showcase Report – Contoso By SpartaBI

      

[Anonymous]

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