Search:

Type: Posts; User: MarcelBeug; Keyword(s):

Page 1 of 6 1 2 3 4

Search: Search took 0.09 seconds.

  1. Re: Power Query Fails to Convert Data Type, Error Persists

    Maybe you just have a crappy query of a crappy csv file.
    We can't help you if you don't share these (may be dummy data as long as it has the same issue).
  2. Re: Power Query Replace Text if it Contains A Certain Word or Group of Words

    Several suggestions are already provided in the cross post on Stack Overflow.
  3. Re: how to do the opposite of merge or combine?

    You're welcome.

    With regard to the formatting, there may be a few points of attention:
    1. During some specific query steps, previously formatting may get lost, so you should make sure that the...
  4. Re: how to do the opposite of merge or combine?

    You can merge the queries with join kind left anti. The following code works if both subsets (Subset1 and Subset2) have the same name of the key column ("Code").
    Alternatively you can merge with...
  5. Replies
    1
    Views
    1,146

    Re: DataFormat.Error: WTF??

    All times must be between 0 and 1. You didn't state which result you are looking for.
    You may either want to change the type to duration or you may first take the modulo of the number and then...
  6. Re: Create list of non-consecutive dates using M...

    gazpage: unlike Excel, in Power Query numbers and dates are incompatible.
    It is possible to add a duration to a date, and it is possible to concatenate dates and times ([date] & [time]), resulting...
  7. Replies
    7
    Views
    3,809

    Re: Adding days to a date using M...

    This looks like a misunderstanding.

    You can select your date column, choose one of the transform options for date (e.g. start of month) and then adjust the generated code to something like:

    =...
  8. Re: Create list of non-consecutive dates using M...

    That would be:


    = List.Dates(List.Min(Source),1+Duration.Days(List.Max(Source)-List.Min(Source))/7,#duration(7,0,0,0))
  9. Replies
    4
    Views
    393

    Re: Vertical Table?

    Or use Transpose in Power Query.
  10. Thread: Date string

    by MarcelBeug
    Replies
    4
    Views
    511

    Re: Date string

    This is how the code was created:

    https://youtu.be/DvhWUC5Wk8M
  11. Thread: Date string

    by MarcelBeug
    Replies
    4
    Views
    511

    Re: Date string

    https://community.powerbi.com/t5/Desktop/Date-string/m-p/328336#M146582


    let
    Source = "Jan 1, 2017 6:01:12 AM PST",
    #"Converted to Table" = #table(1, {{Source}}),
    #"Extracted Text...
  12. Re: Filter by paramert values with if conditions

    You can do it like this. Thanks to lazy evaluation, the filter step will only be executed if yonetici is not "ALL".


    let
    Source = Table.Combine({#"770 Оборот (2)", #"770 Оборот...
  13. Re: Power Query add two columns with a null value

    Just use List.Sum:


    = Table.AddColumn(Source, "Addition", each List.Sum({[Number1],[Number2]}), type number)
  14. Replies
    2
    Views
    410

    Re: LOOKUP Select Column/Field?

    https://bemint-my.sharepoint.com/:i:/g/personal/marcel_beugelsdijk_bemint_nl/ERk16l-MCytCii8xvqzfS4QBirlylASBbvEDl4cGMovf-Q?e=07f2b5d75c26435e8b4d05e3eea892c4

    Query Result:


    let
    Source =...
  15. Replies
    1
    Views
    340

    Re: My input data is running sum

    You can add 2 Index columns, starting with 0 and 1 respectively, then merg the table with itself and perform the calculations.


    let
    Source =...
  16. Replies
    3
    Views
    383

    Re: Custom Column in PQ

    Alternative solution:


    =#time(0,0,0) + ([DeliveryTime] - [SUBMISSION_TIME]) - #time(0,0,0)
  17. Replies
    3
    Views
    383

    Re: Custom Column in PQ

    Assuming that your fields are of type time: if you subtract times, the result is a duration. So if you want to compare the result with a value, you need to use #duration , like this:


    =if...
  18. Replies
    1
    Views
    442

    Re: Power Query help needed

    It is possible. "How", depends on the detailed specifications.
    In the query below the dates are in cell A1 of each worksheet.
    Otherwise the column headers ("Id" and "Text" in this example) are in...
  19. Replies
    2
    Views
    402

    Re: Power Query Custom Column Question

    The approach sounds correct. The formula for the custom column should be like:


    if [Type] = "Debit" then [Amount] * -1 else [Amount]

    If it doesn't work in your case, then you need to provide...
  20. Replies
    2
    Views
    365

    Re: Date / Time import issue

    Probably your queries import the fields as dates instead of date/times.
  21. Re: Extra column that can filter table name from Appended multiple tables

    Power Query has a function for exact this situation: Table.FromPartitions.

    Code for your situation:


    = Table.FromPartitions("Table Name",{{"TableA", TableA},{"TableB",TableB}}, type text)
    ...
  22. Re: Dynamic Column Reference in Power Query

    Add a custom column with formula:


    Record.Field(_,[DoW]&"_Start")
  23. Re: PQ Column Transformation Based on Other Column

    Two more possible ways. Table1 is already imported into Power Query and typed.


    let
    Source = Table1,
    Custom1 = Table.Combine(Table.TransformRows(Source, each if [Value] = "B" then...
  24. Replies
    13
    Views
    1,176

    Re: Unpivot and Arrange the data in Sheet1

    1. Advanced Editor.
    2. "_" refers to each individual current value. In general "_" is used in combination with each. In this case, the current values are the current column names.
    3. I already...
  25. Replies
    2
    Views
    2,245

    Re: LOOKUPVALUE from same table?

    This would be a Power Query solution. I added a temporary index to restore the original sort order at the end.


    let
    Source = Table1,
    #"Added Index" = Table.AddIndexColumn(Source,...
Results 1 to 25 of 150
Page 1 of 6 1 2 3 4