Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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.
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.
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?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
97 | |
96 | |
81 | |
74 | |
66 |
User | Count |
---|---|
129 | |
106 | |
106 | |
86 | |
72 |