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.
Hi,
I'm trying to add months to my date column. But at this moment it is static
I want that to come from my table called 'Table' and column called Dealy Factor.
So if delay factor is changed to 3 then this will add 3 months to my date
FYI : My delay factor comes from my sharepoint list where the users input the delay factor as a parameter
In my actual dataset this table contains a lot of parameters but for simplicity I have included just the 'Delay Factor'
Find the link of file here
Solved! Go to Solution.
Hello @klehar ,
you didn't say they are in one table 😉
You can just do the drill down for each parameter in the second query.
For your example, I added 2 more columns:
In the "Table (2)" query you can add the drilldown as an own variable, like that:
ParameterDelayFactor = Table{0}[Delay Factor],
Or if you need the Parameter 3:
ParameterParameter3 = Table{0}[Parameter 3],
Then you can add it to your normal query of "Table (2)":
let
ParameterDelayFactor = Table{0}[Delay Factor],
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtE31DcyMDJQio0FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "DateAdd", each Date.AddMonths ( [Date], ParameterDelayFactor )),
#"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"DateAdd", type date}})
in
#"Changed Type1"
Hey @klehar ,
you can do that in 2 steps:
1. Go to your "Table", do a right click on your number and chose drill down:
2. Instead of a table this query will show a number, also the symbol will change:
3. Then go to your second "Table (2)" and change the fixed number "2" to "Table":
Congratulations, your added date is now dynamically, based on the result of another query 😊
@selimovd thanks for the quick reply
However as I told you my parameter table has multiple columns with single row
Drilling it down will create a list and not a table
I guess i have to use list functions to fetch some values from each column but not sure how
Hello @klehar ,
you didn't say they are in one table 😉
You can just do the drill down for each parameter in the second query.
For your example, I added 2 more columns:
In the "Table (2)" query you can add the drilldown as an own variable, like that:
ParameterDelayFactor = Table{0}[Delay Factor],
Or if you need the Parameter 3:
ParameterParameter3 = Table{0}[Parameter 3],
Then you can add it to your normal query of "Table (2)":
let
ParameterDelayFactor = Table{0}[Delay Factor],
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtE31DcyMDJQio0FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "DateAdd", each Date.AddMonths ( [Date], ParameterDelayFactor )),
#"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"DateAdd", type date}})
in
#"Changed Type1"
You saved my day Thank you
Sure, here you go:
https://www.swisstransfer.com/d/57685d34-a1b8-4533-81e8-d1359c818e16
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 |
---|---|
117 | |
104 | |
77 | |
73 | |
52 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |