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 data in power bi and I am struggeling getting the data decrypted 😕

 

The VBA Code works fine in MSAccess

 

Public Const CodeKey As String = 1234#




Public Function XORDecryption(DataIn As String) As String

For arkdata1 = 1 To (Len(DataIn) / 2)

intXOrValue1 = Val("&H" & (Mid$(DataIn, (2 * arkdata1) - 1, 2)))

intXOrValue2 = Asc(Mid$(CodeKey, ((arkdata1 Mod Len(CodeKey)) + 1), 1))

strDataOut = strDataOut + Chr(intXOrValue1 Xor intXOrValue2)

Next arkdata1

XORDecryption = strDataOut

End Function

 

Results:

Input: "hello"

encryption("hello"): 5A56585D5D

decryption("5A56585D5D"): hello

 

How can i do in Power Query (or do I need a DAX function?) to decrypt the content for each row?

So far I was not even able to find the corresponding functions that are used in the VBA function..

I saw a similar post but as this is using a different function its not helping me to get this working.

 

Thanks a lot for your support!

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

     

5 Replies

  • 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

         

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Thanks a lot for the great support!