none
PowerQuery getting non-alphabetic characters in a encoded way RRS feed

  • Question

  • Hi everyone, I recently have challenge loading excel workbook that locates in SharePoint to local excel via powerquery. Any advice is welcomed, thanks in advance.

    The challenge is that when I use power query to get data from a excel file in SharePoint, no-alphabetic characters are returned in an encoded way.

    For example, WeekendingName column has data that looks like "WE 2014-03-21" in the Excel from SharePoint, and after I use powerquery to load, the data becomes "WE_x0020_2014-03-21". My only solution now is to load the weird data pattern and do control+F replace those data. I've googled and found there is a XmlConvert.EncodeName Method is able to translate, but not sure how to use it in the power query. Please let me know if you have better solutions for this, thanks a lot!

    Best Regards,

    Jacob



    • Moved by Chenchen Li Wednesday, July 5, 2017 2:29 AM PowerQuery
    Tuesday, July 4, 2017 3:45 AM

Answers

  • Hi Jacob. You should be able to use Power Query's Replace Values command to replace the "_x0020_" escapes, instead of doing it manually each time you load.

    However, that doesn't address the issue of why you're seeing the strange values in the first place. According to this thread, it looks like SharePoint does this kind of replacement when there are spaces in its internal names. But I wouldn't think that this applies to an Excel file hosted in SharePoint, unless you are accessing it as a SharePoint list or something like that.

    Can you share the formula text of your query (View->Advanced Editor)?

    Ehren

    • Marked as answer by Jacob Jun Li Sunday, July 9, 2017 11:42 PM
    Thursday, July 6, 2017 11:14 PM
    Owner

All replies

  • Hello Jacob,

    This forum is for development issues when using Excel Object model. Sorry that your issue is out of scope. I would move this thread into Power Query forum. Thanks for your understanding.

    Regards,

    Celeste


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, July 5, 2017 2:29 AM
  • Thanks Celeste:)
    Wednesday, July 5, 2017 11:54 PM
  • Hi Jacob. You should be able to use Power Query's Replace Values command to replace the "_x0020_" escapes, instead of doing it manually each time you load.

    However, that doesn't address the issue of why you're seeing the strange values in the first place. According to this thread, it looks like SharePoint does this kind of replacement when there are spaces in its internal names. But I wouldn't think that this applies to an Excel file hosted in SharePoint, unless you are accessing it as a SharePoint list or something like that.

    Can you share the formula text of your query (View->Advanced Editor)?

    Ehren

    • Marked as answer by Jacob Jun Li Sunday, July 9, 2017 11:42 PM
    Thursday, July 6, 2017 11:14 PM
    Owner
  • Hi Ehren,

    Thanks a lot for your reply!

    I'm really new to powerquery and not aware of Replace Value command, but I found it just now. Please take a look at below formula text of my power query, thanks!

    let
        Source = Excel.Workbook(Web.Contents("http://t-inform.in.telstra.com.au/sites/NPSAdvocacyFramework/Analysis/DQ Dashboard/test/MOBILITYDATA_RESULT.xlsx"), null, true),
        MOBILITYDATA_RESULT_Sheet = Source{[Item="MOBILITYDATA_RESULT",Kind="Sheet"]}[Data],
        #"Promoted Headers" = Table.PromoteHeaders(MOBILITYDATA_RESULT_Sheet, [PromoteAllScalars=true]),
        #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Metric", type text}, {"WeekEndingName", type text}, {"ProductName", type text}, {"VolumeEpisodes", Int64.Type}, {"PrcEpisodes", type number}}),
        #"Added Custom" = Table.AddColumn(#"Changed Type", "Stream", each "Mobility(Post-paid and Pre-paid)"),
        #"Reordered Columns" = Table.ReorderColumns(#"Added Custom",{"Stream", "Metric", "WeekEndingName", "ProductName", "VolumeEpisodes", "PrcEpisodes"}),
        #"Renamed Columns" = Table.RenameColumns(#"Reordered Columns",{{"ProductName", "Product Name"}}),
        #"Reordered Columns1" = Table.ReorderColumns(#"Renamed Columns",{"Stream", "Product Name", "WeekEndingName", "Metric", "VolumeEpisodes", "PrcEpisodes"}),
        #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Reordered Columns1", {"Stream", "Product Name", "WeekEndingName", "Metric"}, "Attribute", "Value"),
        #"Renamed Columns1" = Table.RenameColumns(#"Unpivoted Columns",{{"Value", "Performance"}}),
        #"Merged Columns" = Table.CombineColumns(#"Renamed Columns1",{"Metric", "Attribute"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Metrics Name")
    in
        #"Merged Columns"

    Best Regards,

    Jacob

    Thursday, July 6, 2017 11:53 PM
  • Ok. I don't see anything in your query which might cause this behavior, so it must be something SharePoint does behind the scenes. Try using Replace Values and let us know if that unblocks you.

    Ehren

    Friday, July 7, 2017 6:03 PM
    Owner
  • Thanks Ehren, I've used replace value and it works fine.
    Sunday, July 9, 2017 11:42 PM