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,
my desired result is to obtain a final table with 4 columns. How can I modifiy the code in my pbi file?
https://1drv.ms/u/s!Aj45jbu0mDVJi1tBwZJ2MKNo7NPi?e=AIWKwU
1) BS_DATE 2) TICKET_NUMBER 3) INCLUDE_IN_REPORTS 4) INDEX
01.11.2022 | INC20221 | 1 | 1 |
02.11.2022 | INC20222 | 1 | 1 |
03.11.2022 | INC20224 | 1 | 1 |
04.11.2022 | 0 | 2 | |
05.11.2022 | 0 | 2 | |
06.11.2022 | INC20227 | 1 | 3 |
07.11.2022 | INC20228 | 1 | 3 |
08.11.2022 | INC20229 | 1 | 3 |
09.11.2022 | INC20230 | 1 | 3 |
10.11.2022 | 0 | 4 |
Thanks in advance!
Solved! Go to Solution.
Hi mangaus1111
I got close with this, but when doing some Googling I actually came across this on this forum. Looks like the same thing. Solved: Create Index that increases every time a value in ... - Microsoft Power BI Community
Hope that helps.
Neil
If I understand you correctly, the solution would be to
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bZNLDsIwDETv0nVVxWOnnzViwYYNK1T1bpyFk9GCkDoZr5I4z5H1RlnXrthgNqAAXd/d7pdjZ+/XfnheH/u69TsDZdAyrky0TJyY792vXPPyqC9O/N6kxMzErMTCxCKEFyKsnIj/cKba3LhNpTmYUGXuTIQSwUTNhlNzXrlNzfnIhJpztm+JObaPzBwsKyayOCeorOCcoLKCI0FVgiPBmA2nsoJzgsoKzgkqKzgSL0pwJMdXRTvc8TfRtJ1y2j4=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"BS_DATE " = _t, TICKET_NUMBER = _t, #" INCLUDE_IN_REPORTS" = _t]),
#"Changed Type1" = Table.TransformColumnTypes(Source,{{"BS_DATE ", type date}, {"TICKET_NUMBER", type text}, {" INCLUDE_IN_REPORTS", type text}}),
#"Trimmed Text" = Table.TransformColumns(#"Changed Type1",{{"TICKET_NUMBER", Text.Trim, type text}, {" INCLUDE_IN_REPORTS", Text.Trim, type text}}),
#"Renamed Columns" = Table.RenameColumns(#"Trimmed Text",{{" INCLUDE_IN_REPORTS", "INCLUDE_IN_REPORTS"}, {"BS_DATE ", "BS_DATE"}}),
#"Replaced Value" = Table.ReplaceValue(#"Renamed Columns","YES","1",Replacer.ReplaceText,{"INCLUDE_IN_REPORTS"}),
#"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","","0",Replacer.ReplaceValue,{"INCLUDE_IN_REPORTS"}),
#"Changed Type" = Table.TransformColumnTypes(#"Replaced Value1",{{"INCLUDE_IN_REPORTS", Int64.Type}}),
//added steps
#"Grouped Rows" = Table.Group(#"Changed Type", {"INCLUDE_IN_REPORTS"},
{{"All", each _, type table [BS_DATE=nullable date, TICKET_NUMBER=text, INCLUDE_IN_REPORTS=nullable number]}}, GroupKind.Local),
#"Added Index" = Table.AddIndexColumn(#"Grouped Rows", "Index", 0, 1, Int64.Type),
#"Removed Columns" = Table.RemoveColumns(#"Added Index",{"INCLUDE_IN_REPORTS"}),
#"Expanded All" = Table.ExpandTableColumn(#"Removed Columns", "All", {"BS_DATE", "TICKET_NUMBER", "INCLUDE_IN_REPORTS"}, {"BS_DATE", "TICKET_NUMBER", "INCLUDE_IN_REPORTS"})
in
#"Expanded All"
Before Grouping:
After Grouping with Index Column Added
Result
Hi @pbix1 ,
I attach a link with a new pbi file. Now the source does not refer to an Excel file and I hope you can see all the script in the advanced editor.
https://1drv.ms/u/s!Aj45jbu0mDVJi1tBwZJ2MKNo7NPi?e=zgvklo
The big problem is not to create the column [INCLUDE_IN_REPORTS], but the column [INDEX].
The column [INDEX] must be increased of 1 anytime the column [INCLUDE_IN_REPORTS] switch from 0 to 1 or from 1 to 0.
If I understand you correctly, the solution would be to
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bZNLDsIwDETv0nVVxWOnnzViwYYNK1T1bpyFk9GCkDoZr5I4z5H1RlnXrthgNqAAXd/d7pdjZ+/XfnheH/u69TsDZdAyrky0TJyY792vXPPyqC9O/N6kxMzErMTCxCKEFyKsnIj/cKba3LhNpTmYUGXuTIQSwUTNhlNzXrlNzfnIhJpztm+JObaPzBwsKyayOCeorOCcoLKCI0FVgiPBmA2nsoJzgsoKzgkqKzgSL0pwJMdXRTvc8TfRtJ1y2j4=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"BS_DATE " = _t, TICKET_NUMBER = _t, #" INCLUDE_IN_REPORTS" = _t]),
#"Changed Type1" = Table.TransformColumnTypes(Source,{{"BS_DATE ", type date}, {"TICKET_NUMBER", type text}, {" INCLUDE_IN_REPORTS", type text}}),
#"Trimmed Text" = Table.TransformColumns(#"Changed Type1",{{"TICKET_NUMBER", Text.Trim, type text}, {" INCLUDE_IN_REPORTS", Text.Trim, type text}}),
#"Renamed Columns" = Table.RenameColumns(#"Trimmed Text",{{" INCLUDE_IN_REPORTS", "INCLUDE_IN_REPORTS"}, {"BS_DATE ", "BS_DATE"}}),
#"Replaced Value" = Table.ReplaceValue(#"Renamed Columns","YES","1",Replacer.ReplaceText,{"INCLUDE_IN_REPORTS"}),
#"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","","0",Replacer.ReplaceValue,{"INCLUDE_IN_REPORTS"}),
#"Changed Type" = Table.TransformColumnTypes(#"Replaced Value1",{{"INCLUDE_IN_REPORTS", Int64.Type}}),
//added steps
#"Grouped Rows" = Table.Group(#"Changed Type", {"INCLUDE_IN_REPORTS"},
{{"All", each _, type table [BS_DATE=nullable date, TICKET_NUMBER=text, INCLUDE_IN_REPORTS=nullable number]}}, GroupKind.Local),
#"Added Index" = Table.AddIndexColumn(#"Grouped Rows", "Index", 0, 1, Int64.Type),
#"Removed Columns" = Table.RemoveColumns(#"Added Index",{"INCLUDE_IN_REPORTS"}),
#"Expanded All" = Table.ExpandTableColumn(#"Removed Columns", "All", {"BS_DATE", "TICKET_NUMBER", "INCLUDE_IN_REPORTS"}, {"BS_DATE", "TICKET_NUMBER", "INCLUDE_IN_REPORTS"})
in
#"Expanded All"
Before Grouping:
After Grouping with Index Column Added
Result
Hi mangaus1111
Couple of issues here. The source refers to an Excel file that's local to you, so I can't modify the code. However, by the look of it, you would just need to do an 'if is null' condition in a conditional column to get the 'include in report flag'. You can do this using the GUI. null needs to be entered as 'null'. Regarding the index, it's not apparent from your sample what you want the index based on. Again there is an add index column in the GUI, but if it needs to be more complex than what this offers, you might need a custom column with some custom code. If you can provide some more detail on what you're after, and change the source to a manual table, (you can just copy/paste from Excel), then myself or someone else might be able to help you further.
Neil
I notice you are a Solution Sage who has provided a lot of solutions to other people. Perhaps I'm missing something?
OK, so I was missing something. I see now how you want to get the index. Not a quick one for me I'm afraid. Hopefully someone else can help.
Maybe incorporate some kind of running sum and combine it with an index.
Hi mangaus1111
I got close with this, but when doing some Googling I actually came across this on this forum. Looks like the same thing. Solved: Create Index that increases every time a value in ... - Microsoft Power BI Community
Hope that helps.
Neil
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.