PowerQueryFormula.FormulaDefinition shows spurious let

Aspose.Cells Product Family
1

I have a workbook (can’t seem to attach it here but happy to share it) which contains this PowerQuery:

(ZIPFile) =>
let
    Header = BinaryFormat.Record([
        MiscHeader = BinaryFormat.Binary(14),
        BinarySize = BinaryFormat.ByteOrder(BinaryFormat.UnsignedInteger32, ByteOrder.LittleEndian),
        FileSize   = BinaryFormat.ByteOrder(BinaryFormat.UnsignedInteger32, ByteOrder.LittleEndian),
        FileNameLen= BinaryFormat.ByteOrder(BinaryFormat.UnsignedInteger16, ByteOrder.LittleEndian),
        ExtrasLen  = BinaryFormat.ByteOrder(BinaryFormat.UnsignedInteger16, ByteOrder.LittleEndian)    
    ]),
 
    HeaderChoice = BinaryFormat.Choice(
        BinaryFormat.ByteOrder(BinaryFormat.UnsignedInteger32, ByteOrder.LittleEndian),
        each if _ <> 67324752             // not the IsValid number? then return a dummy formatter
            then BinaryFormat.Record([IsValid = false, Filename=null, Content=null])
            else BinaryFormat.Choice(
                    BinaryFormat.Binary(26),      // Header payload - 14+4+4+2+2
                    each BinaryFormat.Record([
                        IsValid  = true,
                        Filename = BinaryFormat.Text(Header(_)[FileNameLen]),
                        Extras   = BinaryFormat.Text(Header(_)[ExtrasLen]),
                        Content  = BinaryFormat.Transform(
                            BinaryFormat.Binary(Header(_)[BinarySize]),
                            (x) => try Binary.Buffer(Binary.Decompress(x, Compression.Deflate)) otherwise null
                        )
                        ]),
                        type binary                   // enable streaming
                )
    ),
 
    ZipFormat = BinaryFormat.List(HeaderChoice, each _[IsValid] = true),
 
    Entries = List.Transform(
        List.RemoveLastN( ZipFormat(ZIPFile), 1),
        (e) => [FileName = e[Filename], Content = e[Content] ]
    )
in
    Table.FromRecords(Entries)

When I read this formula via PowerQueryFormula.FormulaDefinition in Aspose.Cells (20.2.0), I get this:

let
let
    Header = BinaryFormat.Record([
        MiscHeader = BinaryFormat.Binary(14),
        BinarySize = BinaryFormat.ByteOrder(BinaryFormat.UnsignedInteger32, ByteOrder.LittleEndian),
        FileSize   = BinaryFormat.ByteOrder(BinaryFormat.UnsignedInteger32, ByteOrder.LittleEndian),
        FileNameLen= BinaryFormat.ByteOrder(BinaryFormat.UnsignedInteger16, ByteOrder.LittleEndian),
        ExtrasLen  = BinaryFormat.ByteOrder(BinaryFormat.UnsignedInteger16, ByteOrder.LittleEndian)    
    ]),
 
    HeaderChoice = BinaryFormat.Choice(
        BinaryFormat.ByteOrder(BinaryFormat.UnsignedInteger32, ByteOrder.LittleEndian),
        each if _ <> 67324752             // not the IsValid number? then return a dummy formatter
            then BinaryFormat.Record([IsValid = false, Filename=null, Content=null])
            else BinaryFormat.Choice(
                    BinaryFormat.Binary(26),      // Header payload - 14+4+4+2+2
                    each BinaryFormat.Record([
                        IsValid  = true,
                        Filename = BinaryFormat.Text(Header(_)[FileNameLen]),
                        Extras   = BinaryFormat.Text(Header(_)[ExtrasLen]),
                        Content  = BinaryFormat.Transform(
                            BinaryFormat.Binary(Header(_)[BinarySize]),
                            (x) => try Binary.Buffer(Binary.Decompress(x, Compression.Deflate)) otherwise null
                        )
                        ]),
                        type binary                   // enable streaming
                )
    ),
 
    ZipFormat = BinaryFormat.List(HeaderChoice, each _[IsValid] = true),
 
    Entries = List.Transform(
        List.RemoveLastN( ZipFormat(ZIPFile), 1),
        (e) => [FileName = e[Filename], Content = e[Content] ]
    )
in
    Table.FromRecords(Entries)

The double let at the beginning happens consistently for any other PowerQuery that starts with something like “(ZIPFile) =>” but seems ok when the PowerQuery starts with “let”. Can you please advise?

2

@bjoern.stiel,

Please zip your template file and attach it here (please use Upload button from the toolbar while replying). Also, paste the sample code using Aspose.Cells APIs on how you are getting the power query formula definition. We will check it soon.

3

ExtractCode.xlsx.zip (18.0 KB)

private void SerializePowerQueryFormulas(IEnumerable<Aspose.Cells.QueryTables.PowerQueryFormula> powerQueryFormulas)
{
    foreach (var powerQueryFormula in powerQueryFormulas)
    {
        var contents = powerQueryFormula.FormulaDefinition.Replace("\r\n", "\n");
        var loc = contents.Split('\n').Count();
        });
    }
}

Let me know if you need anything else.

4

@bjoern.stiel,

Thanks for the file.

I simply used the following lines of code and got an exception “Invalid pPower query formula definition” on the second line:
e.g
Sample code:

Workbook workbook = new Workbook("e:\\test2\\ExtractCode.xlsx");
            Aspose.Cells.QueryTables.DataMashup mashupData = workbook.DataMashup;//exception

I am using latest version/fix of Aspose.Cells APIs. Which version of the product you are using?

5

I’m using 20.2.0

6

@bjoern.stiel,

Thanks for providing further details.

I evaluated using latest version/fix and some older versions. You are right. In previous versions, it was working but it gives duplicate “let”. With newer versions, I got an exception “Invalid pPower query formula definition” when retrieving mashup data from the workbook:
I have logged a ticket with an id “CELLSNET-47494” for your issue. We will look into it to fix it soon.

Once we have an update on it, we will let you know with details/fix.

7

@bjoern.stiel,
We have investigated it bit more. It looks like a function definition, not source and parameter.
We need some time to study it. If there is any update , we will inform you soon.

8

@bjoern.stiel,

This is to inform you that we have fixed your issue now. We will soon provide you the fixed version after performing QA and incorporating other enhancements and fixes.

9

@bjoern.stiel,

Please try our latest version/fix: Aspose.Cells for .NET v20.7.2 (attached)
Aspose.Cells20.7.2 For .Net2_AuthenticodeSigned.Zip (5.4 MB)
Aspose.Cells20.7.2 For .Net4.0.Zip (5.4 MB)

Your issue should be fixed in it.

Let us know your feedback.

10

The issues you have found earlier (filed as CELLSNET-47494) have been fixed in Aspose.Cells for .NET v20.8. This message was posted using Bugs notification tool by Amjad_Sahi