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 Community,
I have two column one is date another is values. I have tried a lot many approaches but not getting any complete result.
So basicaly, value should be shifted to next row as per date and if same date is repeated so same values should be there. If i am not clear so below is the expected ouput.
Input is like below:
Values | Date |
138 | 1/1/2020 |
120 | 1/2/2020 |
80 | 1/3/2020 |
90 | 1/4/2020 |
110 | 1/5/2020 |
124 | 1/1/2020 |
100 | 1/3/2020 |
Expected Output:
Date | Values | Output |
1/1/2020 | 138 | 138 |
1/1/2020 | 124 | 124 |
1/2/2020 | 120 | 124 |
1/3/2020 | 80 | 80 |
1/3/2020 | 100 | 100 |
1/4/2020 | 90 | 100 |
1/5/2020 | 110 | 90 |
TIA!
Solved! Go to Solution.
Hi @Uzi2019 ,
Not really sure how you are getting the sorting done but to get the same sorting I did the following steps in the query editor:
if [Index] = 0
then [Values]
else if [Index] = List.Max(#"Added Index1"[Index]) then #"Added Index1"{[Index] - 1}[Values] else
if (#"Added Index1"{[Index]
- 1}[Date]
= [Date]
= false and #"Added Index1"{[Index] + 1}[Date]
= [Date]
= false)
then #"Added Index1"{[Index] - 1}[Values]
else [Values]
Check full code below:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjS2UNJRMjDUByIjAyMDpVgdoCCQBgoaoQhagMWMUcQswWImqJoNwYKmaCaaYLHGAN3IWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Values = _t, Date = _t]),
#"Added Index" = Table.AddIndexColumn(Source, "Index", 1, 1),
#"Changed Type" = Table.TransformColumnTypes(#"Added Index",{{"Values", Int64.Type}, {"Date", type date}}),
#"Sorted Rows" = Table.Sort(#"Changed Type",{{"Date", Order.Ascending}, {"Index", Order.Ascending}}),
#"Removed Columns" = Table.RemoveColumns(#"Sorted Rows",{"Index"}),
#"Added Index1" = Table.AddIndexColumn(#"Removed Columns", "Index", 0, 1),
#"Added Custom" = Table.AddColumn(#"Added Index1", "Custom", each if [Index] = 0
then [Values]
else if [Index] = List.Max(#"Added Index1"[Index]) then #"Added Index1"{[Index] - 1}[Values] else
if (#"Added Index1"{[Index]
- 1}[Date]
= [Date]
= false and #"Added Index1"{[Index] + 1}[Date]
= [Date]
= false)
then #"Added Index1"{[Index] - 1}[Values]
else [Values])
in
#"Added Custom"
This may need adjustments based on the sorting of your data.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @Uzi2019 ,
Not really sure how you are getting the sorting done but to get the same sorting I did the following steps in the query editor:
if [Index] = 0
then [Values]
else if [Index] = List.Max(#"Added Index1"[Index]) then #"Added Index1"{[Index] - 1}[Values] else
if (#"Added Index1"{[Index]
- 1}[Date]
= [Date]
= false and #"Added Index1"{[Index] + 1}[Date]
= [Date]
= false)
then #"Added Index1"{[Index] - 1}[Values]
else [Values]
Check full code below:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjS2UNJRMjDUByIjAyMDpVgdoCCQBgoaoQhagMWMUcQswWImqJoNwYKmaCaaYLHGAN3IWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Values = _t, Date = _t]),
#"Added Index" = Table.AddIndexColumn(Source, "Index", 1, 1),
#"Changed Type" = Table.TransformColumnTypes(#"Added Index",{{"Values", Int64.Type}, {"Date", type date}}),
#"Sorted Rows" = Table.Sort(#"Changed Type",{{"Date", Order.Ascending}, {"Index", Order.Ascending}}),
#"Removed Columns" = Table.RemoveColumns(#"Sorted Rows",{"Index"}),
#"Added Index1" = Table.AddIndexColumn(#"Removed Columns", "Index", 0, 1),
#"Added Custom" = Table.AddColumn(#"Added Index1", "Custom", each if [Index] = 0
then [Values]
else if [Index] = List.Max(#"Added Index1"[Index]) then #"Added Index1"{[Index] - 1}[Values] else
if (#"Added Index1"{[Index]
- 1}[Date]
= [Date]
= false and #"Added Index1"{[Index] + 1}[Date]
= [Date]
= false)
then #"Added Index1"{[Index] - 1}[Values]
else [Values])
in
#"Added Custom"
This may need adjustments based on the sorting of your data.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsCovering 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 | |
107 | |
69 | |
68 | |
43 |
User | Count |
---|---|
148 | |
103 | |
103 | |
88 | |
66 |