cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Tolle502 Frequent Visitor
Frequent Visitor

Working with Navision Archive and Version import

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_QuantityAmount

60001

1100100000
600012101100500
600013200200000
600021105000
600022115500

And this is what i want: 

 

No_Version No_QuantityAmount
600013200200000
600022115500

 

If you need anymore information please let me know 

Best regards

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Working with Navision Archive and Version import

Hi @Tolle502 ,

 

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

1. Group by the table in power query .

1.PNG

 

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.

Capture.PNG

 

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] )

 

 

 

333.PNG

 

For more details, please chck the pbix as attached.

 

 

 

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

View solution in original post

2 REPLIES 2
Community Support Team
Community Support Team

Re: Working with Navision Archive and Version import

Hi @Tolle502 ,

 

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

1. Group by the table in power query .

1.PNG

 

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.

Capture.PNG

 

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] )

 

 

 

333.PNG

 

For more details, please chck the pbix as attached.

 

 

 

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

View solution in original post

Tolle502 Frequent Visitor
Frequent Visitor

Re: Working with Navision Archive and Version import

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? 

 

 

Helpful resources

Announcements
New Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

Top Solution Authors
Top Kudoed Authors (Last 30 Days)