Working with Navision Archive and Version import

[Tolle502]

Hallo PowerBi Community!

1st post here 

I am trying to import the Sales tables from MS navision, and got a problem with Version No_  then i work with the Archives

I only want the latest version No_  but i cant find a way to filter it

This is what i get from normal import:

No_	Version No_	Quantity	Amount
60001	1	100	100000
60001	2	101	100500
60001	3	200	200000
60002	1	10	5000
60002	2	11	5500

And this is what i want: 

No_	Version No_	Quantity	Amount
60001	3	200	200000
60002	2	11	5500

If you need anymore information please let me know 

Best regards

[v-frfei-msft · Accepted solution]

Hi @Tolle502 ,

To get it in power query. Please check the following steps as below.

1. Group by the table in power query .

2.Expand the columns we need and filter the table.

 #"Filtered Rows" = Table.SelectRows(#"Expanded a", each ([a.Version No_] = [Count])),

3. Then we can get the excepted result.

The full code for your reference.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjMwMDBU0lECYwMDCAkESrE6CEkjsDBUiSmapDFIAVinEapOI7ixQMIUXQYsC5I2BRsYCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [No_ = _t, #"Version No_" = _t, Quantity = _t, Amount = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"No_", Int64.Type}, {"Version No_", Int64.Type}, {"Quantity", Int64.Type}, {"Amount", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"No_"}, {{"Count", each List.Max([Version No_]), type number}, {"a", each _, type table [No_=number, Version No_=number, Quantity=number, Amount=number]}}),
    #"Expanded a" = Table.ExpandTableColumn(#"Grouped Rows", "a", {"Version No_", "Quantity", "Amount"}, {"a.Version No_", "a.Quantity", "a.Amount"}),
    #"Filtered Rows" = Table.SelectRows(#"Expanded a", each ([a.Version No_] = [Count])),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Count"})
in
    #"Removed Columns"

Alternatively, we can make it by a calculated table.

Table 3 =
VAR k =
    ADDCOLUMNS (
        'table',
        "maxno", CALCULATE (
            MAX ( 'table'[Version No_] ),
            FILTER ( 'table', 'table'[No_] = EARLIER ( 'table'[No_] ) )
        )
    )
RETURN
    FILTER ( k, 'table'[Version No_] = [maxno] )

For more details, please chck the pbix as attached.

[Tolle502]

Wow ty so much @v-frfei-msft ! it worked perfectly 😉 

Allright i got a little question, it is posible to do the same with Direct Q?