Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
Power Query only
Hi , i am trying to add to my transaction table , the price from the price list.
transactiontbl:
Customer, Date, Amount
PriceListtbl:
[From Date],[To Date],[Customer],[Price]
Thanks a head,
Solved! Go to Solution.
hi,@Veritas_D
it is the M code in the advanced editor in power query of power bi
you can paste this code in it,
By the way , these steps in the power query are:
step1.Merge table transactiontbl with table Rates
step2.add a custom column
Step3:
Expand column
Step4:Remove the column and result:
Best Regards,
Lin
hi,@Veritas_D
it is the M code in the advanced editor in power query of power bi
you can paste this code in it,
By the way , these steps in the power query are:
step1.Merge table transactiontbl with table Rates
step2.add a custom column
Step3:
Expand column
Step4:Remove the column and result:
Best Regards,
Lin
@Anonymous
Could you copy paste few rows of sample data with expected results?
@Anonymous
You can use this
let Source = Excel.CurrentWorkbook(){[Name="transactiontbl"]}[Content], #"Changed Type" = Table.TransformColumnTypes(Source,{{"Customer", type text}, {"Date", type datetime}, {"Amount", Int64.Type}}), #"Merged Queries" = Table.NestedJoin(#"Changed Type",{"Customer"},Rates,{"Customer"},"Rates",JoinKind.LeftOuter), #"Added Custom" = Table.AddColumn(#"Merged Queries", "Custom", each let mydate= [Date] in Table.SelectRows([Rates],each [From Date] <= mydate and [To Date] >= mydate)), #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Price"}, {"Custom.Price"}), #"Removed Columns" = Table.RemoveColumns(#"Expanded Custom",{"Rates"}) in #"Removed Columns"
Thank you, but i meant power query of power bi
@Anonymous
Please see attached Excel File for the Steps
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
93 | |
85 | |
84 | |
73 | |
49 |
User | Count |
---|---|
142 | |
133 | |
110 | |
68 | |
55 |