Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.
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
Check out the November 2023 Power BI update to learn about new features.
Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.
Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!