Custom Column Power Query needed

[Anonymous]

Hi,

I have a list of id's in one column and a list of values in another column. I want to add a third column for each row on the table by the size of each id. So for instance in the table below, the sum of x is 300 and the sum of y is 170. So want to add a third column to show the grouping based on the sum by each id.. so all rows for x should have a category to say above 300. and all rows of y should have a category to say below 300.

Id amount
x 100
x 50
x 150
y 70
y 100
z 200
z 100
z 60


Please help on how i can show this by adding a custom column in power bi.

[ImkeF · Accepted solution]

Yes, please check out this video:

https://onedrive.live.com/embed?cid=DE165DDF5D02DAFF&resid=DE165DDF5D02DAFF%2180912&authkey=ABoDhTk-...

Just select the other column as well in the last step when you select which fields to show.

[ImkeF]

try this:

let
    Source = YourData,
    #"Grouped Rows" = Table.Group(Source, {"Id"}, {{"Sum", each List.Sum([amount]), type number}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Group", each "The sum of this group is "&Text.From([Sum])),
    #"Merged Queries" = Table.NestedJoin(#"Added Custom",{"Id"},Source,{"Id"},"NewColumn",JoinKind.LeftOuter),
    #"Expanded NewColumn" = Table.ExpandTableColumn(#"Merged Queries", "NewColumn", {"amount"}, {"amount"})
in
    #"Expanded NewColumn"

[Anonymous]

Hi @ImkeF,

Sorry, i'm new to power queries. Could you please send screen shots for each of the steps you have mentioned. I grouped the rows, but could not categorise each row and then merge that into the main data table.

Appreciate if you could send it in screen shot format.

Thanks

[Anonymous]

Hi @ImkeF,

Ok so I managed to do it somehow until expended columns. However, how do I get back the original source table view? there are many other columns in the source table that I need to use. Just want to ensure it's all in one table including the new categorisation exercise.

Thanks.

[ImkeF · Accepted solution]

Yes, please check out this video:

https://onedrive.live.com/embed?cid=DE165DDF5D02DAFF&resid=DE165DDF5D02DAFF%2180912&authkey=ABoDhTk-...

Just select the other column as well in the last step when you select which fields to show.

[Anonymous]

Hi @ImkeF,

Thanks for that. Few clarifications - 

1. I have many other columns in the table that I do not see after merging. Like for instance, I have country name, sales rep name, product name etc etc (which was not in my sample data example). So, how do I view all these columns again after expanding in the last step? When I click on the expand option, I only see the new added custom / conditional column and the IDs.

2. Instead of adding "custom" column,.. I chose to add conditional column and stated if value is below 100, catergory is small value, if below 500, middle value, above 500, high value etc etc. It seemed to work fine as an added column. But i cant seem to get back all other various columns that I need from the data source.

Thanks,

[ImkeF]

could you please upload your file or post screenshots or a video as well?

[Anonymous]

Hi @ImkeF,

Yes, will try and upload a video. But one more clarification I needed, just incase i'm missing something from your video. I find just before the expand step, you change the #"Added Custom" to Source. When I try and do that, it gives me an error to say - "The column 'Id' of the table wasn't found." Any way I can fix this? May be it will work then.

[ImkeF · Accepted solution]

Yes, that's most likely the reason then. You need to reference your original table there whose other values you want to show/expand. If it is not the Source-step, then you have to find the stepname in your query where the data is in the shape that you can use here.

This is the place where your original table will be merged back to the grouped results and you need to join it on the Id-columns of both tables.

[Anonymous]

Thank you so much @ImkeF. You are a rockstar! Woks perfectly now. 🙂