Power Query - Join multiple tables

See also questions close to this topic

• .Body & .HTMLBody Indent and Default Signature Issue

  I've set up a macro to send an e-mail through Outlook.

  .Body is read from a cell inside the file with indents. Since the value will change depending on the usage, I need to reference that cell for the body.

  However, there rises 2 issues using .HTMLbody I lose indents which are constructed with CHAR(10) but I keep the default HTML signature.

  When using just .BODY indents are displayed are correctly however the default signature is not constructed as HTML and I lose the images.

  How should I go about fixing this issue?

  My code:

  sig = .HTMLBody
  body = xlSht.Range("B4").Value
      .To = xlSht.Range("B2").Value
      .CC = ""
      .Subject = xlSht.Range("B1").Value
      .body = body & sig
      .Display
  

  I'd really appreciate your assistance.

  Thanks.
• Yahoo Finance no longer returns VBA cookie request for .getResponseHeader("Set-Cookie")

  The following Excel VBA code segment has worked for years, but stopped working around 28 Apr 2022. I receive the responseText, but the .getResponseHeader("Set-Cookie") returns null.

  Set httpReq = New WinHttp.WinHttpRequest
  
  DownloadURL = "https://finance.yahoo.com/lookup?s=" & stockSymbol
  With httpReq
      .Open "GET", DownloadURL, False
      .setRequestHeader "Content-Type", "application/x-www-form-urlencoded; charset=UTF-8"
      .Send
      .waitForResponse
      response = .responseText
      cookie = Split(.getResponseHeader("Set-Cookie"), ";")(0)
  End With
  
• export excel rows to individual json files in python

  My excel file has 500 rows of data. I am trying to get 500 individual JSON files. Each file should have data only from 1 row. Thank you in advance.

  import json
  import pandas
  excel_data_df = pandas.read_excel("F:/2/N.csv.xlsx", 
  sheet_name='Sheet1')
  json_str = 
  excel_data_df.to_json(orient='records')
  for idx, row in enumerate(json_str):
  fpath = str(idx) + ".json"
  with open(fpath, "w+") as f:
  json.dump(row, f)
  
• Roadmap for PowerBI development

  I have a task to visualize data from an API using PowerBI with periodic updates. What programs will be used in this chain, besides PowerBI? Do I need to use something specific or are there options? What knowledge do I need at each stage to implement this task?

  I found that the data from the API needs to be transferred to the database, from which then to set up a connection to PowerBi, is this enough to start learning?
• Can you use a variable to represent a slicer selection in PowerBi?

  I am having a problem displaying a sum value in a gauge visual. My table has many rows with a value in each along with a date. I wrote a measure that calculates the sum of the values filtered by the type of product and whether it was sold during the day or night. The problem is there are many rows of values with the same date so when I put a date drop down slicer on the page, the measure doesn't return a value unless I make the date slicer a "range" which I don't want. I am assuming that the measure doesn't know which value to return when the slicer is in the drop down mode. How can I make this work? Can I use two variables in the DAX code for the measure that provide a date "range" based on the selection in the date drop down that the user chooses?? See below for my current code.

  Table:

      DATE   |   Product Sold   |   Day or Night
  --------------------------------------------------
  4/1/2022            1000               N
  4/1/2022             500               D
  4/1/2022             800               N
  4/1/2022            2000               D
  4/2/2022             900               N
  
  Measure = CALCULATE(
      SUM('Table'[Product Sold]),
      'Table'[Day or Night] IN { "N" })
  

  So the gauge visual won't display a value based on this measure when using a drop down date slicer. It should display 1,800 when the drop down date slicer has a date of 4/1/2022

  The gauge visual WILL display the sum value when using a date range slicer but I can't use that type.

  I hope this makes sense. There has to be a way around it to make it work. I'm at a loss.
• Count occurances in multiple filtered rows

  I need a little help since im new to PowerBI. I have a data set which says what have been eaten in a specific day. At the end there are columns which show if the day before the overall feeling was better (so in this specific day it got worse). I got up to 30 Ingredients and 5 days before. The "1" in e.g. Day2 means its TRUE for condition "2 days before it got worse"

  The data looks like this: Data set example

  Now, I want to retrieve and add up all ingredients, which has been eaten at "Day1", "Day2" and so on, so I can see which food is maybe causing problems because it should appear more often in the days before or at least appear in every case there. How do I achieve this?

  For example I can see then, that on Day2 overy often Ingredient "Apple" appears, so there could be an assumption that Chicken meat is not good for this person.

  I tried to pivot the table, as well as disconnect the "DayX" into another table and make relationship between them, but nothing adds up the things in the way I want it to.
• compare two separate data sets

  is it possible to compare particular values from a table/report from power bi data set with another table from excel sheet?

  In the power bi data set i have the organization data about all employees, and in my excel i have the data for 10 particular individuals. (something like: Name, Grade, Role, Team, Joining Date, Departure Date)

  What i now do is to create in power bi the report with just the 10 people, export the data and then i compare that table with my local one, if there are any mismatches, like if the grades changed or they move to another team/department or some thing, in my local file i have some custom/aditional columns that arent in the powerbi dataset (Name, Grade, Role, Team, Joining Date, Departure Date, Comment, Skills, Future team)

  i could not load a data set from the excel file, and also from the company powerbi dataset, in the same report/page
• Format date with dax

  I have dates like:

  

  I need to do two transformations:

  Convert it into datetime

  field date and time in the initial data shows in GMT. Necessary translate into our local time as well as our working hours. Local time = time in Kyiv which also takes into account daylight saving time. Working hours = because we work 24/7, we take into account the beginning of each day not from 00:00, and from 7:25 am. That is, if the purchase was made on February 1 at 7:15, it has enroll until January 31. For the second point I used this function, how to midify it to respond to the requirement please?

  let
  Source = (datetimecolumn as datetime) =>
  
  let
  date = DateTime.Date(datetimecolumn),
  time = DateTime.Time(datetimecolumn),
  lastSundayOfOctober = Date.StartOfWeek(#date(Date.Year(date), 10, 31), Day.Sunday),
  lastSundayOfMarch = Date.StartOfWeek(#date(Date.Year(date), 3, 31), Day.Sunday),
  
  isSummerTime = (date = lastSundayOfMarch and time >= #time(2,0,0))
  or
  (date > lastSundayOfMarch and date < lastSundayOfOctober)
  or
  (date = lastSundayOfOctober and time <= #time(2,0,0)),
  timeZone = 1 + Number.From(isSummerTime),
  CET =
  DateTime.From(date)
  + #duration(0,Time.Hour(time),Time.Minute(time),Time.Second(time))
  + #duration(0, timeZone, 0, 0)
  in
  CET
  in
  Source 2e
  
• How to get record before and after in forall loop powerapps

  I am trying to run a function that returns a table with a corresponding date index and date text. This is so that I can display the information in a tutorial screen. However, I am confused on how to access to the previous record. Here is my pseudocode:

  ForAll( Tweets (sorted by crf1d_date_index),
          If(the record IS NOT the LAST record,
               If('crf1d_date_index' != 'crf1d_date_index' of the NEXT record,
                       { 
                             Step: crf1d_date_index, 
                             Text: crf1d_tweet_time 
                       }
                 )
             )
  
          If(the record IS the LAST record,
               If('crf1d_date_index' != 'crf1d_date_index' of the PREVIOUS record),
                       { 
                             Step: crf1d_date_index, 
                             Text: crf1d_tweet_time 
                       }
                 )
  
  )