Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
Hi,
I have a set of original data that I have already grouped by, and this is working just fine.
[DELETED]
I apply this code to this data :
#"Changed Type" = Table.TransformColumnTypes(#"Filtered Rows1",{{"Source.Name", type text}, {"Team", type text}, {"Project category", type text}, {"Project type", type text}, {"Role", type text}, {"Month", type text}, {"Value", type number
#"Grouped Rows" = Table.Group(#"Changed Type", {"Team", "Project category", "Month"}, {{"Count", each _, type table [Source.Name=nullable text, Team=nullable text, Project category=nullable text, Project type=nullable text, Role=nullable text, Month=nullable text, Value=nullable number, Index=number]}}),
#"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([Count], "#FCST", 1, 1))
I get the following output :
[DELETED]
This output is fine, but I would like the #FCST column to iterate only when the value of Source.Name changes. Would you have any clue about how I could do this please ?
Thanks in advance,
Solved! Go to Solution.
Hi,
I finally solved my issue with this trick :
#"Grouped Rows" = Table.Group(#"Changed Type", {"Team", "Project category", "Month"}, {{"Count", each _, type table [Source.Name=nullable text, Team=nullable text, Project category=nullable text, Project type=nullable text, Role=nullable text, Month=nullable text, Value=nullable number, Index=number]}}),
#"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddRankColumn([Count], "#FCST", {"Source.Name", Order.Ascending}, [RankKind = RankKind.Dense])),
#"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Project type", "Role", "Value", "#FCST"}, {"Project type", "Role", "Value", "#FCST"}),
Hi, @nicplancho ;
I create a simple example. then you could add a funtion.
= Table.Group(#"Changed Type", {"Cate"}, {{"Index", each Table.AddIndexColumn(_ , "Index", 0, 1, Int64.Type)}})
Then expend it.
Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTK1UIrVgTBNLOFMiKATkGVkCGciWEZwljGcZQlnWSCLxQIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Cate = _t, Value = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Cate", type text}, {"Value", Int64.Type}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"Cate"}, {{"Index", each Table.AddIndexColumn(_ , "Index", 0, 1, Int64.Type)}}),
#"Expanded Index" = Table.ExpandTableColumn(#"Grouped Rows", "Index", {"Index"}, {"Index.Index"})
in
#"Expanded Index"
Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi Yalan Wu,
Here is what I have played :
#"Grouped Rows" = Table.Group(#"Changed Type", {"Team", "Project category", "Month"}, {{"Index", each Table.AddIndexColumn(_ , "Index", 0, 1, Int64.Type)}}),
#"Expanded Index" = Table.ExpandTableColumn(#"Grouped Rows", "Index", {"Project type", "Role", "Value","Index"}, {"Project type", "Role", "Value", "Index.Index"})
Here is the result :
Given that I have only 2 source files, the index should not go beyond two values, but it goes up to 30 values here unfortunately.
Hi,
I finally solved my issue with this trick :
#"Grouped Rows" = Table.Group(#"Changed Type", {"Team", "Project category", "Month"}, {{"Count", each _, type table [Source.Name=nullable text, Team=nullable text, Project category=nullable text, Project type=nullable text, Role=nullable text, Month=nullable text, Value=nullable number, Index=number]}}),
#"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddRankColumn([Count], "#FCST", {"Source.Name", Order.Ascending}, [RankKind = RankKind.Dense])),
#"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Project type", "Role", "Value", "#FCST"}, {"Project type", "Role", "Value", "#FCST"}),
Hi, @nicplancho ;
May be:
#"Grouped Rows" = Table.Group(#"Changed Type", {"Team", "Project category","Project type", "Month"}, {{"Index", each Table.AddIndexColumn(_ , "Index", 0, 1, Int64.Type)}}),
#"Expanded Index" = Table.ExpandTableColumn(#"Grouped Rows", "Index", { "Role", "Value","Index"}, {"Project type", "Role", "Value", "Index.Index"})
Or can you share the result what you want to output to let me understand you logic?
Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.