Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Good Day - I have a table in PowerBI Query as DATA [shown below]
I am trying to convert that DATA to OUTPUT [shown below]
In summary, Each ROW needs to be duplicated based on COUNT value, and ID Column needs to be added for that set of duplicate data.
Appreciate any help to make this possible!
Solved! Go to Solution.
Hi @hackfifi
You could try this calculated table in DAX
Table 2 = GENERATE( 'Table', FILTER( SELECTCOLUMNS( GENERATESERIES(1,100), "ID",[Value] ), [ID]<=[Count]) )
This gave me this result
Hi @hackfifi
You could try this calculated table in DAX
Table 2 = GENERATE( 'Table', FILTER( SELECTCOLUMNS( GENERATESERIES(1,100), "ID",[Value] ), [ID]<=[Count]) )
This gave me this result
I need to do the same thing but in Power Query. Any idea how?
@Phil_Seamark - Mate you never fail to disappoint!
Couple of Queries:
1. Using the Generate Table function, i assume now i cannot accesss the new generated table via Query Editor to make some changes?
2. I am trying to understand your formular - what does "GenerateSeries(1,100) do?
Thanks again!
Hi @hackfifi
The GenerateSeries function just creates a single column table with values between 1 and 100
if you create a new calculated table and use just that function you will see what it looks like.
I wrapped it with the SELECTCOLUMNS function, simply to rename the column as you already had a column called [Value] in your first column.
Ok @Phil_Seamark - So i am doing SOMETHING wrong. Obviously the below the "REAL" data, not the SAMPLE table i provided earlier. As you can see each row has count value of "19" in this case. Hence the generated table should show each row 19 times i.e. it should have 57 rows of data
Yes the "Count" column on Main Table is of Type "Whole Number"....
Maybe i will import the data and start again
User | Count |
---|---|
102 | |
91 | |
87 | |
79 | |
71 |
User | Count |
---|---|
113 | |
105 | |
101 | |
75 | |
64 |