how do i auto populate columns with specific date

[Anonymous]

i have a data set grouped in to rows of 7

each group of 7 are connected by a unique id number

each row is a specific question

there is a comments box that provides the answer

i have created calculated columns to extrapolate the answer for each

                                                                            tax         invoice     date

number 112            question 1 tax                    answer1    blank     blank

number 112            question 2 invoice              blank       answer2  blank

number 112            question 3  date                 blank       blank       answer3

number 113            question 1  tax                   answer     blank       blank

number 113            question 2 invoice              blank       answer    blank

number 113            question 3 date                  blank       blank       answer

is it possible to auto populate the cells for each column with the answer for each unique number id?

                                                                              tax            invoice     date

number 112            question 1  Tax                   answer 1    answer2  answer 3

number 112            question 2   Invoice            answer 1    answer2  answer 3

number 112            question 3   Date                answer 1    answer2  answer 3

number 113            question 1   Tax                  answer 1    answer2  answer 3

number 113            question 2   Invoice            answer 1    answer2  answer 3

number 113            question 3   Date                answer 1    answer2  answer 3

i am never really used Power Bi and i am learning as i go

any help would be appreciated 😊

[v-robertq-msft]

Hi, @Anonymous 

According to your description and sample data, I think you can achieve the expected data using the “replace values” in the Power Query, like this:

You can do the operation for each of the three columns:

And you can get what you want, like this:

This is the M code in the advanced editor, which you can copy and paste into yours:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WyivNTUotUjA0NFLSUVIoLE0tLsnMz1MwBPJKEiuAZGJecXlqEYgPQrE6OLWAeJl5ZfmZyakQxRCdRgS0GQN5KYklUD1wbcaoeoxJd50xea4zJtJ1sQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [number = _t, question = _t, name = _t, Column1 = _t, Column2 = _t, Column3 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"number", type text}, {"question", type text}, {"name", type text}, {"Column1", type text}, {"Column2", type text}, {"Column3", type text}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type","","answer1",Replacer.ReplaceValue,{"Column1"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","","answer2",Replacer.ReplaceValue,{"Column2"}),
    #"Replaced Value2" = Table.ReplaceValue(#"Replaced Value1","","answer3",Replacer.ReplaceValue,{"Column3"})
in
    #"Replaced Value2"

You can download my test pbix file here

If this result is not what you want, you can post some sample data(without sensitive data) and your expected result.

How to Get Your Question Answered Quickly 

Best Regards,

Community Support Team _Robert Qin

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

[Anonymous]

hi @v-robertq-msft 

as below the table as it would probably appear

 

[Anonymous]

Hi  @v-robertq-msft 

i think the problem i have is this that the answers to any of the 7 questions  will almost always be different for each unique ID

so answer 1 for unique ID 112 will be different to answer 1 for unique ID 113

Power Bi would need to be able to

• reference the unique ID before populating a cell, knowing to only go down 6 ( i cell will already have the answer to copy ) cells before the next unique ID is reached

i feel this may be too complex a query to solve in power bi or the data must be maniuplated prior to being uploaded

also forgot to mention currently my data source is an excel sheet

 

[Anonymous]

Hi @v-robertq-msft 

thank you for the code

i will try this with my data today and see if it works

i will report back if it does or post some sample data if it doesnt

thank you again!!

[Anonymous]

Forgot to mention the reason i am asking is when i display this data in table format i am limited to what i can filter because some data will appear against the unique ID and the rest appears against blank