Forum Discussion

ksab23's avatar
ksab23
Helper I
3 years ago
Solved

Create new dataset with blanks

Hi All,  I was tasked to create a rather complex report that would showcase all users from a given list that participated in any of our offered videos. However, I was given two datasets - one t...
Need Help
Show and Tell
Tutorial Requests
  • olgad's avatar
    olgad
    3 years ago

    Hi there, 
    first replace Registration value yes with 1. 

    then pick a video title and pivot it

    Then merge two tables on the name

    Then expand it 

    Then replace null in all columns by referencing  that table

    let
    Source = #"Table",
    ReplaceNulls = Table.TransformColumns(Source,{},(x) => Replacer.ReplaceValue(x,null,0))
    in
    ReplaceNulls

     

     

     

    Hope that helps

     

     

ITManuel's avatar
ITManuel
Responsive Resident
3 years ago

Hi ksab23 ,

 

try the following steps in PowerQuery.

 

1st. Pivot the column video tile in Dataset 2 

2nd. Merge Dataset 1 with Dataset 2 

3rd. Expand video columns

  • ksab23's avatar
    ksab23
    Helper I
    3 years ago

    Hello, this seems to work somewhat but I don't think it is good for my needs, as my datasets consist of way too many rows. The list of unique users is 20k+ rows and the one with video titles and respective registrations is even longer, as users can participate in one or more videos. Thus the merged dataset takes ages to load outside of the power query and any action, such as filtering, takes way too long.

    Do you perhaps know if there is a way to tweak this buffering?