Patterns Suggestion Box

[vc_row css=”.vc_custom_1426110781359{padding-top: 50px !important;padding-bottom: 50px !important;}” to_equal_column=”” to_para_bg=”no_bg” to_parallax=”” to_side_img_position=”left” to_para_img_repeat=”no-repeat” to_para_blur=”” to_para_over_set=”” to_para_over_opacity=”0.8″][vc_column width=”1/1″][themeone_header subtitle=”Explain us your scenario or upvote for the one you consider we should work on. Your scenario might become our next recipe or dish :)” type=”h1″ txtalign=”txt-left” decor=”” decorheight=”2px”]

Have any ideas for a new pattern? Share your thoughts!

[/themeone_header][/vc_column][/vc_row][vc_row][vc_column][vc_column_text][raw]

[/raw][/vc_column_text][/vc_column][/vc_row]

Responses

Your email address will not be published. Required fields are marked *

  1. Hi Everybody, has the question of Ted (regarding only adding new files) been answered?
    As of January I have a similar situation and I am curious what the most efficient way would be (in terms of loading) when adding one large csv file (600k rows) per month.
    Should I work this out via append query or can I just drop it in the folder and let power query pick up the files in the folder (running the risk everything in the directory is loaded every time I refresh). Thanks in advance,

    Michiel

    1. Hey Michiel,

      The short answer is that, currently, Power Query does not support incremental loading. Instead, it’ll always refresh the whole query and reprocess the whole table, in other words, it’ll always try to combine all of the files from your folder and not just the new ones that you have in your folder.

      There are workarounds to make Power Query think that is doing an incremental loading, but is more of a hack and a hassle and something not supported by Microsoft. If you want you can absolutely vote for this feature to be added in a future version of Power Query here:
      https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/7288623-incremental-data-loads

      Best!

  2. Hello again,
    Hopefully it is a simple one.
    I have a folder with 60 files in it (.csv and .xls, xlsx) and each week there are 4 new ones being saved.
    is there a way of adding only the new ones without refreshing the previous 60?
    Many thanks,
    Ted

  3. Hi Guys,
    Is there a way to connect to a sharepoint folder that consist of several xl files?
    i know how to connect to a sharepoint list but couldn’t find any material on the folder?

    1. Hey Ted,

      There’s three ways to get content from Sharepoint today, but that’s it. They are:

      -SharePoint.Files(“https://url”)
      -SharePoint.Contents(“https://url”)
      -SharePoint.Tables(“https://url”)

      Hopefully one of those will get you to what you need.

  4. Hi, Im stuck:)
    This is my scenario:
    I have created a distinct list of Company Names {“Company A”, “Company B”, etc} and I want to lookup those names in my fact table where company name is concatenated with some random text.
    So I need to check if a company name exists in fact column and return clean company name.
    Please help.
    Cheers
    Ted

    1. Hey Ted! The scenario that you’re referring to is a common case. It can be solved using a Fuzzy Lookup solution like this one:
      https://msdn.microsoft.com/en-us/library/ms137786.aspx
      Unfortunately, this is a really complicated algorithm that would need to come as a new feature of Power Query. Since there is already a Fuzzy Lookup for SQL Server, its a safe bet that Power Query will have this Fuzzy Lookup one day. I’d recommend you contact the Power Query team through their feedback system (frown or smile icon on the Power Query editor window) and let them know that you are interested in having a Fuzzy Lookup feature on Power Query.

  5. Here’s one I could use help on:
    Multiple stacked data: Months in rows in column A, Multiple customers with 2 or more columns of data each across columns, then stacked by years.
    set up as follows:

    1990, Customer1,,,Customer2,,,Customer3,,, (etc.)

    Month,heading1,heading2,heading3,heading4,heading1,heading2,heading3,heading4,heading1,heading2,heading3,heading4, (etc,.)

    Jan,c1h1data,c1h2data,c1h3data,c1h4data,c2h1data,c2h2data,c2h3data,c2h4data,c3h1data,c3h2data,c3h3data,c3h4data, (etc.)

    [repeat for all months]

    separator row

    1991, Customer1,,,Customer2,,,Customer3,,, (etc.)

    Month,heading1,heading2,heading3,heading4,heading1,heading2,heading3,heading4,heading1,heading2,heading3,heading4, (etc,.)

    Jan,c1h1data,c1h2data,c1h3data,c1h4data,c2h1data,c2h2data,c2h3data,c2h4data,c3h1data,c3h2data,c3h3data,c3h4data, (etc.)

    [repeat for all months]

    separator row

    [repeat for multiple years]

    1. Hey Kathy!
      That might require some specific coding, but I’d try to first break the query into multiple sub-queries or sub-tables. Do you think that you could email us your data set to [email protected]? we’d love to take a closer look at it!

      1. Sending it now as a csv file. Sorry it took me so long to get back to you with this!

        I also sent a second email with the code I’ve managed to come up with. Be gentle… I’m still a newbie at this!

Magic-Tricks-for-Data-Wizards-1.png

ARE YOU IN?

15K+ subscribers have already claimed their free eBook.

Sign up and get your FREE copy