Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

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 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"

Need Help
  • SpartaBI's avatar
    SpartaBI
    4 years ago

    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


          

7 Replies

  • SpartaBI's avatar
    SpartaBI
    Community Champion
    4 years ago

    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's avatar
      Anonymous
      Not applicable
      4 years ago

      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

  • SpartaBI's avatar
    SpartaBI
    Community Champion
    4 years ago

    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's avatar
      Anonymous
      Not applicable
      4 years ago

      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)