Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Decrypt Values from Sharpoint List

Hello, I just started in the world of Power BI and now im facing a challenge. In a sharepoint list I am storing values which are encrypted using a function within MS Access. Now I want to use this ...
Need Help
  • artemus's avatar
    artemus
    5 years ago

    Looks like I didn't properly translate the VBA text offset with the Power Query offset.

     

    This should work:

    = (DataIn as text) as text =>
let
   asBinary = Binary.FromText(DataIn, BinaryEncoding.Hex),
   numbers = Binary.ToList(asBinary),
   xored = List.Transform({0 .. List.Count(numbers) - 1}, each 
      Number.BitwiseXor(numbers{_}, Character.ToNumber(Text.At(CodeKey, Number.Mod(_ + 1, Text.Length(CodeKey)))))
   ),
   asDecodedBinary = Binary.FromList(xored),
   asText = Text.FromBinary(asDecodedBinary, TextEncoding.Ascii)
in
   asText

     

artemus's avatar
artemus
Microsoft Employee
5 years ago

The function would look something like (input into advanced editor after creating a blank query):

(DataIn as text) as text =>
let
   asBinary = Binary.FromText(DataIn, BinaryEncoding.Hex),
   numbers = Binary.ToList(asBinary),
   xored = List.Transform({0 .. List.Count(numbers) - 1}, each 
      Number.BitwiseXor(numbers{_}, Character.ToNumber(Text.At(CodeKey, Number.Mod(_, Text.Length(CodeKey)))))
   ),
   asDecodedBinary = Binary.FromList(xored),
   asText = Text.FromBinary(asDecodedBinary, TextEncoding.Ascii)
in
   asText

 

However, without the CodeKey, I can't check if this works or not.

 

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Thanks a lot for your support!

 

The CodeKey is the very first line (1234) of the code section. 

I added the function with the line 

 CodeKey = Text.Proper("1234#"),
(with # and without # as this doesn't make a difference within VBA)

When Using the function with the input 5A56585D5D the result is kdkil instead of hello

 

Any Idea what I made wrong?

 

  • artemus's avatar
    artemus
    Microsoft Employee
    5 years ago

    Looks like I didn't properly translate the VBA text offset with the Power Query offset.

     

    This should work:

    = (DataIn as text) as text =>
let
   asBinary = Binary.FromText(DataIn, BinaryEncoding.Hex),
   numbers = Binary.ToList(asBinary),
   xored = List.Transform({0 .. List.Count(numbers) - 1}, each 
      Number.BitwiseXor(numbers{_}, Character.ToNumber(Text.At(CodeKey, Number.Mod(_ + 1, Text.Length(CodeKey)))))
   ),
   asDecodedBinary = Binary.FromList(xored),
   asText = Text.FromBinary(asDecodedBinary, TextEncoding.Ascii)
in
   asText

     

  • Icey's avatar
    Icey
    Community Support
    5 years ago

    Hi Anonymous ,

     

     

     CodeKey = Text.Proper("1234#"),
    (with # and without # as this doesn't make a difference within VBA)

     

    One note:

    In Power Query Editor, with "#" and without "#" is not the same. When using artemus 's method, it is needed to remove "#" in your expression.

     

     

    Best regards

    Icey

     

    If this post helps, then consider Accepting it as the solution to help other members find it faster.