I need to create a dynamic column where i can manually enter data in each row

[Anonymous]

Hi All,

Can you please help me for the below issue

I want to create "Amount" column in a table in which i can manually enter each value row by row just like we do in excel i.e.

12

1

3

4

Note: Count of rows is dynamic will increase or decrease.

Based on "Amount" we have to calculate "Target". Target=sum of Amount based on Category.

I tried using parameter but issue is not able to add parameter in each row of column.

Is it possible in PowerBI to create a dynamic column where we can manually enter data in each row?

Any help will be appreciated. Thanks

[v-eqin-msft · Accepted solution]

Hi @Anonymous ,

Maybe you could try these steps in Power Query.

1. Add a Index column to the original table.

2. Create a new blank query, below is the steps and the whole M syntax:

'

let
    Source = {12,1,3,4},
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Added Index" = Table.AddIndexColumn(#"Converted to Table", "Index", 1, 1, Int64.Type),
    #"Renamed Columns" = Table.RenameColumns(#"Added Index",{{"Column1", "Amount"}})
in
    #"Renamed Columns"

You could manually change values by changing this part:

3. Then merge this two tables:

Use Group by sum by each category, the whole M syntax is shown below:

let
    Source = Table.NestedJoin(Table, {"Index"}, Query1, {"Index"}, "Query1", JoinKind.FullOuter),
    #"Expanded Query1" = Table.ExpandTableColumn(Source, "Query1", {"Amount"}, {"Amount"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Query1",{"Index"}),
    #"Grouped Rows" = Table.Group(#"Removed Columns", {"Category"}, {{"Custom", each _, type table [Amount=number]} ,{"Target", each List.Sum([Amount]), type number}  }),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Grouped Rows", "Custom", {"Amount"}, {"Amount"})
in
    #"Expanded Custom"

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@Anonymous , You can not do that. You can create a table with Enter data and merge it with this table

How to Enter Data and Edit it: https://youtu.be/5nE7YGT72kU

Or can use power app to update the table at source https://community.powerbi.com/t5/Power-Platform-Integrations/Power-Apps-Power-BI-Better-together-Write-Back-From-BI/m-p/1044976#M16