问:
Implement an input with a mask
I would like to implement a mask for a text input field which accepts a date. The masked value should display directly inside of the input.

Something like this:

```<code><input type='text' value='____/__/__'>
</code>```

I wrote the mask as a value in that example, but my intent is to allow people to write a date without typing / or - to separate months, years and days. The user should be able to enter numbers into the displayed field, while the mask enforces the format automatically as the user types.

I have seen this behavior on other sites, but I have no idea how it works or how to implement it myself.

答:
I wrote a similar solution some time ago.

Of course it's just a PoC and can be improved further.

This solution covers the following features:

Seamless character input

Pattern customization

Live validation while you typing

Full date validation (including correct days in each month and a leap year consideration)

Descriptive errors, so the user will understand what is going on while he is unable to type a character

Fix cursor position and prevent selections

Show placeholder if the value is empty

const patternFreeChar = "_";
const dayValidator = [/^[0-3]$/, /^0[1-9]|[12]\d|3[01]$/];
const monthValidator = [/^[01]$/, /^0[1-9]|1[012]$/];
const yearValidator = [/^[12]$/, /^19|20$/, /^(19|20)\d$/, /^(19|20)\d\d$/];

/**
 * Validate a date as your type.
 * @param {string} date The date in the provided format as a string representation.
 * @param {string} format The format to use.
 * @throws {Error} When the date is invalid.
 */
function validateStartTypingDate(date, format='DDMMYYYY') {
  if ( !date ) return "";

  date = date.substr(0, 8);

  if ( !/^\d+$/.test(date) )
    throw new Error("Please type numbers only");

  const formatAsArray = format.split('');
  const dayIndex = formatAsArray.findIndex(c => c == 'D');
  const monthIndex = formatAsArray.findIndex(c => c == 'M');
  const yearIndex = formatAsArray.findIndex(c => c == 'Y');

  const dayStr = date.substr(dayIndex,2);
  const monthStr = date.substr(monthIndex,2);
  const yearStr = date.substr(yearIndex,4);

  if ( dayStr && !dayValidator[dayStr.length-1].test(dayStr) ) {
    switch (dayStr.length) {
      case 1:
        throw new Error("Day in month can start only with 0, 1, 2 or 3");
      case 2:
        throw new Error("Day in month must be in a range between 01 and 31");
    }
  }

  if ( monthStr && !monthValidator[monthStr.length-1].test(monthStr) ) {
    switch (monthStr.length) {
      case 1:
        throw new Error("Month can start only with 0 or 1");
      case 2:
        throw new Error("Month number must be in a range between 01 and 12");
    }
  }

  if ( yearStr && !yearValidator[yearStr.length-1].test(yearStr) ) {
    switch (yearStr.length) {
      case 1:
        throw new Error("We support only years between 1900 and 2099, so the full year can start only with 1 or 2");
      default:
        throw new Error("We support only years between 1900 and 2099, so the full year can start only with 19 or 20");
    }
  }

  const day = parseInt(dayStr);
  const month = parseInt(monthStr);
  const year = parseInt(yearStr);
  const monthName = new Date(0,month-1).toLocaleString('en-us',{month:'long'});

  if ( day > 30 && [4,6,9,11].includes(month) )
    throw new Error(`${monthName} have maximum 30 days`);

  if ( day > 29 && month === 2 )
    throw new Error(`${monthName} have maximum 29 days`);

  if ( date.length === 8 ) {
    if ( !isLeap(year) && month === 2 && day === 29 )
      throw new Error(`The year you are trying to enter (${year}) is not a leap year. Thus, in this year, ${monthName} can have maximum 28 days`);
  }

  return date;
}

/**
 * Check whether the given year is a leap year.
 */
function isLeap(year) {
  return new Date(year, 1, 29).getDate() === 29;
}

/**
 * Move cursor to the end of the provided input element.
 */
function moveCursorToEnd(el) {
  if (typeof el.selectionStart == "number") {
    el.selectionStart = el.selectionEnd = el.value.length;
  } else if (typeof el.createTextRange != "undefined") {
    el.focus();
    var range = el.createTextRange();
    range.collapse(false);
    range.select();
  }
}

/**
 * Move cursor to the end of the self input element.
 */
function selfMoveCursorToEnd() {
  return moveCursorToEnd(this);
}

const inputs = document.querySelectorAll("input");

inputs.forEach(input => {
  const { format, pattern } = input.dataset;
  input.addEventListener("keydown", function(event){
    event.preventDefault();
    document.getElementById("date-error-msg").innerText = "";

    // On digit pressed
    let inputMemory = this.dataset.inputMemory || "";

    if ( event.key.length === 1 ) {
      try {
        inputMemory = validateStartTypingDate(inputMemory + event.key, format);
      } catch (err) {
        document.getElementById("date-error-msg").innerText = err.message;
      }
    }

    // On backspace pressed
    if ( event.code === "Backspace" ) {
      inputMemory = inputMemory.slice(0, -1);
    }

    // Build an output using a pattern
    if ( this.dataset.inputMemory !== inputMemory ) {
      let output = pattern;
      for ( let i=0, digit; i<inputMemory.length, digit=inputMemory[i]; i++ ) {
        output = output.replace(patternFreeChar, digit);
      }
      this.dataset.inputMemory = inputMemory;
      this.value = output;
    }

    // Clean the value if the memory is empty
    if ( inputMemory === "" ) {
      this.value = "";
    }
  }, false);

  input.addEventListener('select', selfMoveCursorToEnd, false);
  input.addEventListener('mousedown', selfMoveCursorToEnd, false);
  input.addEventListener('mouseup', selfMoveCursorToEnd, false);
  input.addEventListener('click', selfMoveCursorToEnd, false);
});```
<pre class="snippet-code-css lang-css prettyprint-override"><code>input {
  width: 250px;
}</code>```

<input type="text" placeholder="DD/MM/YYYY" data-format="DDMMYYYY" data-pattern="__/__/____" />

<input type="text" placeholder="MM/DD/YYYY" data-format="MMDDYYYY" data-pattern="__/__/____" />

<input type="text" placeholder="YYYY-MM-DD" data-format="YYYYMMDD" data-pattern="____-__-__" />

<input type="text" placeholder="Day: DD, Year: YYYY, Month: MM" data-format="DDYYYYMM" data-pattern="Day: __, Year: ____, Month: __" />

```

A link to jsfiddle:
https://jsfiddle.net/sm3xw61n/2/

Good luck!

Title: Implement an input with a mask

Body:
I would like to implement a mask for a text input field which accepts a date. The masked value should display directly inside of the input.

Something like this:

<input type='text' value='____/__/__'>

I wrote the mask as a value in that example, but my intent is to allow people to write a date without typing / or - to separate months, years and days. The user should be able to enter numbers into the displayed field, while the mask enforces the format automatically as the user types.

I have seen this behavior on other sites, but I have no idea how it works or how to implement it myself.

Body:
I wrote a similar solution some time ago.

Of course it's just a PoC and can be improved further.

This solution covers the following features:

Seamless character input

Pattern customization

Live validation while you typing

Full date validation (including correct days in each month and a leap year consideration)

Descriptive errors, so the user will understand what is going on while he is unable to type a character

Fix cursor position and prevent selections

Show placeholder if the value is empty

const patternFreeChar = "_";
const dayValidator = [/^[0-3]$/, /^0[1-9]|[12]\d|3[01]$/];
const monthValidator = [/^[01]$/, /^0[1-9]|1[012]$/];
const yearValidator = [/^[12]$/, /^19|20$/, /^(19|20)\d$/, /^(19|20)\d\d$/];

/**
 * Validate a date as your type.
 * @param {string} date The date in the provided format as a string representation.
 * @param {string} format The format to use.
 * @throws {Error} When the date is invalid.
 */
function validateStartTypingDate(date, format='DDMMYYYY') {
  if ( !date ) return "";

  date = date.substr(0, 8);

  if ( !/^\d+$/.test(date) )
    throw new Error("Please type numbers only");

  const formatAsArray = format.split('');
  const dayIndex = formatAsArray.findIndex(c => c == 'D');
  const monthIndex = formatAsArray.findIndex(c => c == 'M');
  const yearIndex = formatAsArray.findIndex(c => c == 'Y');

  const dayStr = date.substr(dayIndex,2);
  const monthStr = date.substr(monthIndex,2);
  const yearStr = date.substr(yearIndex,4);

  if ( dayStr && !dayValidator[dayStr.length-1].test(dayStr) ) {
    switch (dayStr.length) {
      case 1:
        throw new Error("Day in month can start only with 0, 1, 2 or 3");
      case 2:
        throw new Error("Day in month must be in a range between 01 and 31");
    }
  }

  if ( monthStr && !monthValidator[monthStr.length-1].test(monthStr) ) {
    switch (monthStr.length) {
      case 1:
        throw new Error("Month can start only with 0 or 1");
      case 2:
        throw new Error("Month number must be in a range between 01 and 12");
    }
  }

  if ( yearStr && !yearValidator[yearStr.length-1].test(yearStr) ) {
    switch (yearStr.length) {
      case 1:
        throw new Error("We support only years between 1900 and 2099, so the full year can start only with 1 or 2");
      default:
        throw new Error("We support only years between 1900 and 2099, so the full year can start only with 19 or 20");
    }
  }

  const day = parseInt(dayStr);
  const month = parseInt(monthStr);
  const year = parseInt(yearStr);
  const monthName = new Date(0,month-1).toLocaleString('en-us',{month:'long'});

  if ( day > 30 && [4,6,9,11].includes(month) )
    throw new Error(`${monthName} have maximum 30 days`);

  if ( day > 29 && month === 2 )
    throw new Error(`${monthName} have maximum 29 days`);

  if ( date.length === 8 ) {
    if ( !isLeap(year) && month === 2 && day === 29 )
      throw new Error(`The year you are trying to enter (${year}) is not a leap year. Thus, in this year, ${monthName} can have maximum 28 days`);
  }

  return date;
}

/**
 * Check whether the given year is a leap year.
 */
function isLeap(year) {
  return new Date(year, 1, 29).getDate() === 29;
}

/**
 * Move cursor to the end of the provided input element.
 */
function moveCursorToEnd(el) {
  if (typeof el.selectionStart == "number") {
    el.selectionStart = el.selectionEnd = el.value.length;
  } else if (typeof el.createTextRange != "undefined") {
    el.focus();
    var range = el.createTextRange();
    range.collapse(false);
    range.select();
  }
}

/**
 * Move cursor to the end of the self input element.
 */
function selfMoveCursorToEnd() {
  return moveCursorToEnd(this);
}

const inputs = document.querySelectorAll("input");

inputs.forEach(input => {
  const { format, pattern } = input.dataset;
  input.addEventListener("keydown", function(event){
    event.preventDefault();
    document.getElementById("date-error-msg").innerText = "";

    // On digit pressed
    let inputMemory = this.dataset.inputMemory || "";

    if ( event.key.length === 1 ) {
      try {
        inputMemory = validateStartTypingDate(inputMemory + event.key, format);
      } catch (err) {
        document.getElementById("date-error-msg").innerText = err.message;
      }
    }

    // On backspace pressed
    if ( event.code === "Backspace" ) {
      inputMemory = inputMemory.slice(0, -1);
    }

    // Build an output using a pattern
    if ( this.dataset.inputMemory !== inputMemory ) {
      let output = pattern;
      for ( let i=0, digit; i<inputMemory.length, digit=inputMemory[i]; i++ ) {
        output = output.replace(patternFreeChar, digit);
      }
      this.dataset.inputMemory = inputMemory;
      this.value = output;
    }

    // Clean the value if the memory is empty
    if ( inputMemory === "" ) {
      this.value = "";
    }
  }, false);

  input.addEventListener('select', selfMoveCursorToEnd, false);
  input.addEventListener('mousedown', selfMoveCursorToEnd, false);
  input.addEventListener('mouseup', selfMoveCursorToEnd, false);
  input.addEventListener('click', selfMoveCursorToEnd, false);
});

input {
  width: 250px;
}

<div><input type="text" placeholder="DD/MM/YYYY" data-format="DDMMYYYY" data-pattern="__/__/____" /></div>
<div><input type="text" placeholder="MM/DD/YYYY" data-format="MMDDYYYY" data-pattern="__/__/____" /></div>
<div><input type="text" placeholder="YYYY-MM-DD" data-format="YYYYMMDD" data-pattern="____-__-__" /></div>
<div><input type="text" placeholder="Day: DD, Year: YYYY, Month: MM" data-format="DDYYYYMM" data-pattern="Day: __, Year: ____, Month: __" /></div>
<div id="date-error-msg"></div>

A link to jsfiddle:
https://jsfiddle.net/sm3xw61n/2/

Good luck!


来源: stackexchange

问:
ECS unable to place task despite increasing instance count
I'm facing the following problem when creating new instances and increasing the container desired count at the same time. Since the instances are not running when I increase the desired count, I get a "service XXX was unable to place a task because no container instance met all of its requirements.". A few seconds later the new instances are up, however, the cluster still has "Desire count: 30, Pending count: 0, Running count: 3". In other words, the cluster does not "know" that there are new instances and no new containers are created.

How can I avoid this situation? Is there a parameter that instructs the cluster to monitor the instance count other than immediately after an increase in desired count?

答:
In this case its an expected behavior of ECS, reason being that ECS service scheduler includes circuit breaker logic that throttles how often tasks are placed if they repeatedly fail to launch.

When a new container instance in spined up it takes some time to get
  register to the Cluster and it looks like service is getting throttled
  because time taken from increase in desired count to registration of
  container instances to the cluster.

Having said that, if you wait for ~15 minutes after scaling number of instance in the cluster, Service scheduler will start placing the task on new container instances.

To avoid this situation, ECS Cluster should be autoscaled based on Custer reservation metric, by this ECS cluster will have additional capacity beforehand to accommodate new task count.

and here is a tutorial on scaling ECS cluster.

ContentLicense: CC BY-SA 4.0


问:
bootstrap 4 tabs with buttons with active class not working
i have a small issue concerning my bootstrap 4 tabs:

My intention is too switch between buttons like this example: https://codepen.io/anon/pen/vwQamK but with bootstrap.

```<code>  <div id="tab" class="btn-group btn-group-justified" data-toggle="buttons">
    <a href="#prices" class="btn btn-primary active" data-toggle="tab">
      <input type="radio" />Prices
    </a>
    <a href="#features" class="btn btn-default" data-toggle="tab">
      <input type="radio" />Features
    </a>
  </div>

  <div class="tab-content">
    <div class="tab-pane active" id="prices">Prices content</div>
    <div class="tab-pane" id="features">Features Content</div>
  </div>
</code>```

Currently the toggle between the tab content is not working!

Here is my codepen:

https://codepen.io/user1010/pen/mYQvVo

答:
With .nav-pills

You can use .nav-pills: https://getbootstrap.com/docs/4.3/components/navs/#pills

            Prices

            Features

Prices content

Features Content

```
<p><img src="https://i.stack.imgur.com/9qUiw.png" alt="enter image description here" /></p>
<p><strong>demo:</strong> https://jsfiddle.net/davidliang2008/dxzaufho/12/</p>
<p><br><br></p>
<h3>With btn-group and custom styles</h3>
<pre class="lang-html prettyprint-override"><code><div class="container">
    <div class="nav btn-group">

            Prices

            Features

    </div>
    <div class="tab-content">
        <div class="tab-pane active" id="prices">Prices content</div>
        <div class="tab-pane" id="features">Features Content</div>
    </div>
</div>
</code>```

.btn-group.nav {
    display: inline-flex;
}

.btn-group .btn.active {
    background-color: var(--primary);
    color: #fff;
}
```

demo: https://jsfiddle.net/davidliang2008/dxzaufho/19/

Regarding to your issue that your tab is not working, I've opened an issue in Github. Currently it looks like you need to have either .nav or .list-group as the tab's parent class, otherwise the tab won't work properly.

Body:
i have a small issue concerning my bootstrap 4 tabs:

My intention is too switch between buttons like this example: https://codepen.io/anon/pen/vwQamK but with bootstrap.

      <input type="radio" />Prices

      <input type="radio" />Features

Prices content

Features Content

Currently the toggle between the tab content is not working!

Here is my codepen:

https://codepen.io/user1010/pen/mYQvVo

Body:
With .nav-pills

You can use .nav-pills: https://getbootstrap.com/docs/4.3/components/navs/#pills

<div class="container">
    <div class="nav nav-pills">
        <a href="#prices" class="nav-link active" data-toggle="pill">
            Prices
        </a>
        <a href="#features" class="nav-link" data-toggle="pill">
            Features
        </a>
    </div>
    <div class="tab-content">
        <div class="tab-pane active" id="prices">Prices content</div>
        <div class="tab-pane" id="features">Features Content</div>
    </div>
</div>

demo: https://jsfiddle.net/davidliang2008/dxzaufho/12/

With btn-group and custom styles

<div class="container">
    <div class="nav btn-group">
        <a href="#prices" class="btn active" data-toggle="tab">
            Prices
        </a>
        <a href="#features" class="btn" data-toggle="tab">
            Features
        </a>
    </div>
    <div class="tab-content">
        <div class="tab-pane active" id="prices">Prices content</div>
        <div class="tab-pane" id="features">Features Content</div>
    </div>
</div>

.btn-group.nav {
    display: inline-flex;
}

.btn-group .btn.active {
    background-color: var(--primary);
    color: #fff;
}

demo: https://jsfiddle.net/davidliang2008/dxzaufho/19/

Regarding to your issue that your tab is not working, I've opened an issue in Github. Currently it looks like you need to have either .nav or .list-group as the tab's parent class, otherwise the tab won't work properly.


问:
How to add Icon to Pyinstaller in Windows 10
I modified the spec file to include the icon but every time I generate the exe, the system is throwing this error:

```<code>2105 INFO: Writing RT_ICON 1 resource with 40 bytes
Traceback (most recent call last):
  File "c:\program files (x86)\microsoft visual studio\shared\python36_64\lib\site-packages\win32ctypes\pywin32\pywintypes.py", line 35, in pywin32error
    yield
  File "c:\program files (x86)\microsoft visual studio\shared\python36_64\lib\site-packages\win32ctypes\pywin32\win32api.py", line 269, in UpdateResource
    handle, type, name, language, lp_data, len(lp_data))
  File "c:\program files (x86)\microsoft visual studio\shared\python36_64\lib\site-packages\win32ctypes\core\cffi\_resource.py", line 134, in _UpdateResource
    function_name='UpdateResource')
  File "c:\program files (x86)\microsoft visual studio\shared\python36_64\lib\site-packages\win32ctypes\core\cffi\_util.py", line 81, in __call__
    self._raise_error(function_name)
  File "c:\program files (x86)\microsoft visual studio\shared\python36_64\lib\site-packages\win32ctypes\core\cffi\_util.py", line 92, in _raise_error
    raise exception
OSError: [WinError 87] The parameter is incorrect
</code>```

I tried generating the exe file directly from pyinstaller using --onefile and also from .spec file.

block_cipher = None

Add the ico to exe file

答:
Try this code:</p>

```<code>from os import path
script_dir = path.dirname(path.abspath(SPEC))
exe = EXE(pyz,
         a.scripts,
         a.binaries,
         a.zipfiles,
         a.datas,
         x, y,
         name='super_program',
         debug=False,
         strip=False,
         upx=True,
         runtime_tmpdir=None,
         console=True,
         icon=os.path.join(script_dir, 'icon.ico'))
</code>```

Body:
I modified the spec file to include the icon but every time I generate the exe, the system is throwing this error:

2105 INFO: Writing RT_ICON 1 resource with 40 bytes
Traceback (most recent call last):
  File "c:\program files (x86)\microsoft visual studio\shared\python36_64\lib\site-packages\win32ctypes\pywin32\pywintypes.py", line 35, in pywin32error
    yield
  File "c:\program files (x86)\microsoft visual studio\shared\python36_64\lib\site-packages\win32ctypes\pywin32\win32api.py", line 269, in UpdateResource
    handle, type, name, language, lp_data, len(lp_data))
  File "c:\program files (x86)\microsoft visual studio\shared\python36_64\lib\site-packages\win32ctypes\core\cffi\_resource.py", line 134, in _UpdateResource
    function_name='UpdateResource')
  File "c:\program files (x86)\microsoft visual studio\shared\python36_64\lib\site-packages\win32ctypes\core\cffi\_util.py", line 81, in __call__
    self._raise_error(function_name)
  File "c:\program files (x86)\microsoft visual studio\shared\python36_64\lib\site-packages\win32ctypes\core\cffi\_util.py", line 92, in _raise_error
    raise exception
OSError: [WinError 87] The parameter is incorrect

I tried generating the exe file directly from pyinstaller using --onefile and also from .spec file.

block_cipher = None

Add the ico to exe file

Body:
Try this code:

from os import path
script_dir = path.dirname(path.abspath(SPEC))
exe = EXE(pyz,
         a.scripts,
         a.binaries,
         a.zipfiles,
         a.datas,
         x, y,
         name='super_program',
         debug=False,
         strip=False,
         upx=True,
         runtime_tmpdir=None,
         console=True,
         icon=os.path.join(script_dir, 'icon.ico'))


问:
Laravel API returns a view 404 error instead of JSON error
I am trying to create a RESTful API using laravel, I'm trying to fetch a resource with an invalid ID, and the result is 404 since it is not found, but my problem is the response is not in JSON format, but a View 404 (by default) with HTML. Is there any way to convert the response into JSON? For this situation, I use Homestead.

I try to include a fallback route, but it does not seem to fit this case.

Route::fallback(function () {
    return response()->json(['message' => 'Not Found.'], 404);
});
```

<p>I try to modify the Handler (App\Exceptions), but nothing change.</p>

<pre class="lang-php prettyprint-override"><code>public function render($request, Exception $e)
{
    if ($e instanceof ModelNotFoundException) {
        if ($request->ajax()) {
            return response()->toJson([
                'message' => 'Not Found.',
            ], 404);
        }
    }

    return parent::render($request, $e);
}
</code>```

答:
You'll need to send the correct Accept header in your request: 'Accept':'application/json'.

Then Illuminate\Foundation\Exceptions\Handler will care of the formatting in the render method in your response:

```<code>return $request->expectsJson()
                    ? $this->prepareJsonResponse($request, $e)
                    : $this->prepareResponse($request, $e);
</code>```

Body:
I am trying to create a RESTful API using laravel, I'm trying to fetch a resource with an invalid ID, and the result is 404 since it is not found, but my problem is the response is not in JSON format, but a View 404 (by default) with HTML. Is there any way to convert the response into JSON? For this situation, I use Homestead.

I try to include a fallback route, but it does not seem to fit this case.

Route::fallback(function () {
    return response()->json(['message' => 'Not Found.'], 404);
});

I try to modify the Handler (App\Exceptions), but nothing change.

public function render($request, Exception $e)
{
    if ($e instanceof ModelNotFoundException) {
        if ($request->ajax()) {
            return response()->toJson([
                'message' => 'Not Found.',
            ], 404);
        }
    }

    return parent::render($request, $e);
}

Body:
You'll need to send the correct Accept header in your request: 'Accept':'application/json'.

Then Illuminate\Foundation\Exceptions\Handler will care of the formatting in the render method in your response:

return $request->expectsJson()
                    ? $this->prepareJsonResponse($request, $e)
                    : $this->prepareResponse($request, $e);


问:
Pandas.Series.dtype.kind is None for pd.interval
Test code:

s = pd.Series(pd.array([pd.Interval(0,1.2), pd.Interval(5,123)]))
s.dtype
s.dtype.kind is None

>>> interval[float64]
>>> True
```

Is it some bug or made intentionally? If latter - for what reason?

答: The reason this is appearing as None is simply because the implementation of IntervalDtype explicitly sets kind = None.  This should probably be updated to 'O', though some care is needed here as it will result in unintended side effects, e.g. this would cause is_string_dtype to return True (see here).

Body:
Test code:

s = pd.Series(pd.array([pd.Interval(0,1.2), pd.Interval(5,123)]))
s.dtype
s.dtype.kind is None

>>> interval[float64]
>>> True

Is it some bug or made intentionally? If latter - for what reason?


问:
Execute all javascript functions in sequence
I want a function which will execute all my functions in the sequence they wrote in ExecuteINsequence function. I want a generic ExecuteINsequence function to which i will pass any 3 functions which should be executed one after other.

```<code>ExecuteINsequence(functionOne, [p1, p2], functionTwo, [p1, p2], functionThree, [p1, p2]);

function ExecuteINsequence(f1, f1_params, f2, f2_params, f3, f3_params) {
  f1.apply(this, f1_params);
  f2.apply(this, f2_params);
  f3.apply(this, f3_params);
}

function functionOne(p1, p2) {
  console.log("one");
  $.ajax({
      url: "www.url.com",
      type: "POST",
      cache: false,
      success: function(data) {
        functionFour()
      );
    }
  });
}

function functionTwo(p1, p2) {
  setTimeout(function() {
    console.log("two");
  }, 2000);

}

function functionThree(p1, p2) {
  setTimeout(function() {
    console.log("three");
  }, 1000);
}

function functionFour() {
  setTimeout(function() {
    console.log("four");
  }, 3000);

}
</code>```

expected Result:

one

four

two

three

Actual reuslt:

one

three
two
four

答:
The reason this happens is because while your functions are in order, they perform async actions (the ajax and set timeout call).

The functions all get started in the right order, but due to their timings end in another. If you add a console.log as first line in your functions (and not in the onComplete's as they now are), you'll see they start in the order you expect.

For the ajax-request, that is whats called a Promise. It will always do something at the end, but you have to "connect" to it:

```<code>function functionOne(p1, p2) {
  console.log("one");
  return $.ajax({
      url: "www.url.com",
      type: "POST",
      cache: false,
      success: function(data) {
        functionFour()
      );
    }
  });
}

functionOne(1,2).then(functionTwo);
</code>```

You might also want to check out the async + await combination. Promises and async matter can get complex very quickly, so I recommend doing a little more research at those topics to clear things up. It takes a few times to understand them.

Body:
I want a function which will execute all my functions in the sequence they wrote in ExecuteINsequence function. I want a generic ExecuteINsequence function to which i will pass any 3 functions which should be executed one after other.

ExecuteINsequence(functionOne, [p1, p2], functionTwo, [p1, p2], functionThree, [p1, p2]);

function ExecuteINsequence(f1, f1_params, f2, f2_params, f3, f3_params) {
  f1.apply(this, f1_params);
  f2.apply(this, f2_params);
  f3.apply(this, f3_params);
}

function functionOne(p1, p2) {
  console.log("one");
  $.ajax({
      url: "www.url.com",
      type: "POST",
      cache: false,
      success: function(data) {
        functionFour()
      );
    }
  });
}

function functionTwo(p1, p2) {
  setTimeout(function() {
    console.log("two");
  }, 2000);

}

function functionThree(p1, p2) {
  setTimeout(function() {
    console.log("three");
  }, 1000);
}

function functionFour() {
  setTimeout(function() {
    console.log("four");
  }, 3000);

}

expected Result:

one

four

two

three

Actual reuslt:

one

three
two
four

Body:
The reason this happens is because while your functions are in order, they perform async actions (the ajax and set timeout call).

The functions all get started in the right order, but due to their timings end in another. If you add a console.log as first line in your functions (and not in the onComplete's as they now are), you'll see they start in the order you expect.

For the ajax-request, that is whats called a Promise. It will always do something at the end, but you have to "connect" to it:

function functionOne(p1, p2) {
  console.log("one");
  return $.ajax({
      url: "www.url.com",
      type: "POST",
      cache: false,
      success: function(data) {
        functionFour()
      );
    }
  });
}

functionOne(1,2).then(functionTwo);

You might also want to check out the async + await combination. Promises and async matter can get complex very quickly, so I recommend doing a little more research at those topics to clear things up. It takes a few times to understand them.


问:
How can I improve my code for generating a dynamic UI element?
I have a dynamic Shiny UI element, where the user is prompted to select a number (1-4) from a dropdown. Shiny then responds by generating the corresponding number of numericInput() elements that will be used by my program.

The following code works, but it's very long-winded and I imagine there are methods to improve the code to (1) make it more concise, and (2) avoid using if/else statements.

library(shiny)

ui <- fluidPage(
  sidebarLayout(
    sidebarPanel(
      selectInput("num", label = h3("Select box"),
                  choices = list("Choice 1" = 1, "Choice 2" = 2, "Choice 3" = 3,
                                 "Choice 4" = 4),
                  selected = 1),
      uiOutput("input_ui")
    ),
    mainPanel(
      tableOutput("table")
    )
  )
)

server <- function(input, output) {

  output$input_ui <- renderUI({
    num <- as.integer(input$num)
    if (num == 1) {
      fluidRow(
        column(6, numericInput("min", h5("Min"), value = 10)))
    } else if (num == 2) {
      fluidRow(
        column(6, numericInput("min", h5("Min"), value = 10)),
        column(6, numericInput("max", h5("Max"), value = 15))
      )
    } else if (num == 3) {
      fluidRow(
        column(6, numericInput("min", h5("Min"), value = 10)),
        column(6, numericInput("max", h5("Max"), value = 15)),
        column(6, numericInput("max", h5("Max"), value = 20))
      )
    } else if (num == 4) {
      fluidRow(
        column(6, numericInput("min", h5("Min"), value = 10)),
        column(6, numericInput("max", h5("Max"), value = 15)),
        column(6, numericInput("max", h5("Max"), value = 20)),
        column(6, numericInput("max", h5("Max"), value = 25))
      )
    }
  })
}

shinyApp(ui = ui, server = server)
```

Is there a better way to write this?

Note that in the above, the values and labels are arbitrary.

答:
The answer from @phalteman is good. An alternative is to remove the server side code completely which is good in its own way.

We write a function which creates a taglist of inputs and use a conditional panel to detect which input is being selected.

```<code>library(shiny)

multiple_numeric_input <- function(names){
  taglst = tagList()
  for (name in names){
    taglst = tagAppendChild(taglst,column(6,numericInput(name,name,value = 10)))
  }
  return(fluidRow(taglst))
}

ui <- fluidPage(
  sidebarLayout(
    sidebarPanel(
      selectInput("num", label = h3("Select box"),
                  choices = list("Choice 1" = 1, "Choice 2" = 2, "Choice 3" = 3,
                                 "Choice 4" = 4),
                  selected = 1),
      conditionalPanel("input.num == 1",
                       multiple_numeric_input(c("min"))),
      conditionalPanel("input.num == 2",
                       multiple_numeric_input(c("min","max"))),
      conditionalPanel("input.num == 3",
                       multiple_numeric_input(c("min","max","min2"))),
      conditionalPanel("input.num == 4",
                       multiple_numeric_input(c("min","max","min2","max2")))
    ),
    mainPanel(
      tableOutput("table")
    )
  )
)

server <- function(input, output) {}

shinyApp(ui = ui, server = server)
</code>```

Body:
I have a dynamic Shiny UI element, where the user is prompted to select a number (1-4) from a dropdown. Shiny then responds by generating the corresponding number of numericInput() elements that will be used by my program.

The following code works, but it's very long-winded and I imagine there are methods to improve the code to (1) make it more concise, and (2) avoid using if/else statements.

library(shiny)

ui <- fluidPage(
  sidebarLayout(
    sidebarPanel(
      selectInput("num", label = h3("Select box"),
                  choices = list("Choice 1" = 1, "Choice 2" = 2, "Choice 3" = 3,
                                 "Choice 4" = 4),
                  selected = 1),
      uiOutput("input_ui")
    ),
    mainPanel(
      tableOutput("table")
    )
  )
)

server <- function(input, output) {

  output$input_ui <- renderUI({
    num <- as.integer(input$num)
    if (num == 1) {
      fluidRow(
        column(6, numericInput("min", h5("Min"), value = 10)))
    } else if (num == 2) {
      fluidRow(
        column(6, numericInput("min", h5("Min"), value = 10)),
        column(6, numericInput("max", h5("Max"), value = 15))
      )
    } else if (num == 3) {
      fluidRow(
        column(6, numericInput("min", h5("Min"), value = 10)),
        column(6, numericInput("max", h5("Max"), value = 15)),
        column(6, numericInput("max", h5("Max"), value = 20))
      )
    } else if (num == 4) {
      fluidRow(
        column(6, numericInput("min", h5("Min"), value = 10)),
        column(6, numericInput("max", h5("Max"), value = 15)),
        column(6, numericInput("max", h5("Max"), value = 20)),
        column(6, numericInput("max", h5("Max"), value = 25))
      )
    }
  })
}

shinyApp(ui = ui, server = server)

Is there a better way to write this?

Note that in the above, the values and labels are arbitrary.

Body:
The answer from @phalteman is good. An alternative is to remove the server side code completely which is good in its own way.

We write a function which creates a taglist of inputs and use a conditional panel to detect which input is being selected.

library(shiny)

multiple_numeric_input <- function(names){
  taglst = tagList()
  for (name in names){
    taglst = tagAppendChild(taglst,column(6,numericInput(name,name,value = 10)))
  }
  return(fluidRow(taglst))
}

ui <- fluidPage(
  sidebarLayout(
    sidebarPanel(
      selectInput("num", label = h3("Select box"),
                  choices = list("Choice 1" = 1, "Choice 2" = 2, "Choice 3" = 3,
                                 "Choice 4" = 4),
                  selected = 1),
      conditionalPanel("input.num == 1",
                       multiple_numeric_input(c("min"))),
      conditionalPanel("input.num == 2",
                       multiple_numeric_input(c("min","max"))),
      conditionalPanel("input.num == 3",
                       multiple_numeric_input(c("min","max","min2"))),
      conditionalPanel("input.num == 4",
                       multiple_numeric_input(c("min","max","min2","max2")))
    ),
    mainPanel(
      tableOutput("table")
    )
  )
)

server <- function(input, output) {}

shinyApp(ui = ui, server = server)


问:
Git Tag failed with code 128: fatal: unterminated line in .git/packed-refs
I am having an error in SourceTree that says, "Error Encountered: 'git tag' failed with code 128: fatal: unterminated line in .git/packed-refs" If anyone has seen this error and knows a solution I would be grateful.</p>

答:
Just delete the file .git/packed-refs.

Then "refresh" your repo with a pull or fetch.

I had the same error fatal: unterminated line in .git/packed-refs: ...

(And that file was just lines of:

0000 0000 0000 0000 0000 0000 0000 0000)

In my case deleting the file worked and I was able to use my repo again.

Failing the above, I would try something like mentioned in this answer.  The steps are:

move old git directory

initialise new repo

link to old repo (by remote add origin)

get old history (fetch)

force update to old history  (by doing a reset)


问:
What is the stripe webhook event for all types of incoming payments?
There is a ton of different stripe webhook event types, but I can't seem to find the one I'm looking for.

Of the list, these three seem most applicable:

```<code>charge.succeeded: Occurs whenever a new charge is created and is successful.

invoice.payment_succeeded: Occurs whenever an invoice payment attempt succeeds.

order.payment_succeeded: Occurs whenever an order payment attempt succeeds.

transfer.paid: Occurs after a transfer is paid. For Instant Payouts, the event will be sent on the next business day, although the funds should be received well beforehand.
</code>```

But I'm not sure if there's any overlap between these, and have a hard time believing there isn't one, all encompassing, you-just-got-paid webhook event.

Do I have to create webhooks for all of these?  Or is there a webhook event that does that already?

答: charge.succeeded will fire every time one of your customers is successfully charged. The others are for more specific types of situations, but that one will fire for a successful payment in general.

Body:
There is a ton of different stripe webhook event types, but I can't seem to find the one I'm looking for.

Of the list, these three seem most applicable:

charge.succeeded: Occurs whenever a new charge is created and is successful.

invoice.payment_succeeded: Occurs whenever an invoice payment attempt succeeds.

order.payment_succeeded: Occurs whenever an order payment attempt succeeds.

transfer.paid: Occurs after a transfer is paid. For Instant Payouts, the event will be sent on the next business day, although the funds should be received well beforehand.

But I'm not sure if there's any overlap between these, and have a hard time believing there isn't one, all encompassing, you-just-got-paid webhook event.

Do I have to create webhooks for all of these?  Or is there a webhook event that does that already?


问:
Using Kaggle Datasets in Google Colab
Is it possible to use any datasets available via the kaggle API in Google Colab? I see the Kaggle API is used in this Colab notebook, but it's a bit unclear to me what datasets it provides access to.

答:
Combined the top response to this Github gist as Colab Implementation. You can directly copy the code and use it.

How to Import a Dataset from Kaggle in Colab

Method:

First a few things you have to do:

Sign up for Kaggle

Sign up for a competition you want to access data from (for example LANL-Earthquake-Prediction competition).

Download your credentials to access Kaggle API as kaggle.json

# Install kaggle packages
!pip install -q kaggle
!pip install -q kaggle-cli
```

<pre class="lang-py prettyprint-override"><code># Colab's file access feature
from google.colab import files

# Upload `kaggle.json` file
uploaded = files.upload()
</code>```

# Retrieve uploaded file
# print results
for fn in uploaded.keys():
  print('User uploaded file "{name}" with length {length} bytes'.format(
      name=fn, length=len(uploaded[fn])))

# Then copy kaggle.json into the folder where the API expects to find it.
!mkdir -p ~/.kaggle
!cp kaggle.json ~/.kaggle/
!chmod 600 ~/.kaggle/kaggle.json
!ls ~/.kaggle
```

<p>Now check if it worked!  </p>

<pre class="lang-py prettyprint-override"><code>#list competitions
!kaggle competitions list -s LANL-Earthquake-Prediction
</code>```

Title: Using Kaggle Datasets in Google Colab

Body:
Combined the top response to this Github gist as Colab Implementation. You can directly copy the code and use it.

How to Import a Dataset from Kaggle in Colab

Method:

First a few things you have to do:

Sign up for Kaggle

Sign up for a competition you want to access data from (for example LANL-Earthquake-Prediction competition).

Download your credentials to access Kaggle API as kaggle.json

# Install kaggle packages
!pip install -q kaggle
!pip install -q kaggle-cli

# Colab's file access feature
from google.colab import files

# Upload `kaggle.json` file
uploaded = files.upload()

# Retrieve uploaded file
# print results
for fn in uploaded.keys():
  print('User uploaded file "{name}" with length {length} bytes'.format(
      name=fn, length=len(uploaded[fn])))

# Then copy kaggle.json into the folder where the API expects to find it.
!mkdir -p ~/.kaggle
!cp kaggle.json ~/.kaggle/
!chmod 600 ~/.kaggle/kaggle.json
!ls ~/.kaggle

Now check if it worked!

#list competitions
!kaggle competitions list -s LANL-Earthquake-Prediction


答:
Callbacks is what allows asynchronous functions to continue processing in sequence. This is true for all languages: c++ (lambda callbacks), Java (lambda callbacks and CompletionStage), go, Python etc. - not just for javascript.

So to solve this you need to do the following:

```<code>ExecuteINsequence(functionOne, [p1, p2], functionTwo, [p1, p2], functionThree, [p1, p2]);

function ExecuteINsequence(f1, f1_params, f2, f2_params, f3, f3_params) {
  var self = this;

  f1.apply(this, f1_params,function(){
    // self is necessary in here because we are no
    // longer calling f2 in the context of ExecuteINsequence():
    f2.apply(self, f2_params,function(){
      // same reasoning for self:
      f3.apply(self, f3_params);
    });
  });
}

function functionOne(p1, p2, callback) {
  console.log("one");
  $.ajax({
      url: "www.url.com",
      type: "POST",
      cache: false,
      success: function(data) {
        functionFour(callback)
      );
    }
  });
}

function functionTwo(p1, p2,callback) {
  setTimeout(function() {
    console.log("two");
    if (typeof callback == 'function') {
      callback();
    }
  }, 2000);

}

function functionThree(p1, p2, callback) {
  setTimeout(function() {
    console.log("three");
    if (typeof callback == 'function') {
      callback();
    }
  }, 1000);
}

function functionFour(callback) {
  setTimeout(function() {
    console.log("four");
    if (typeof callback == 'function') {
      callback();
    }
  }, 3000);

}
</code>```

Alternatively, if you are using promises you can use async/await:

```<code>ExecuteINsequence(functionOne, [p1, p2], functionTwo, [p1, p2], functionThree, [p1, p2]);

async function ExecuteINsequence(f1, f1_params, f2, f2_params, f3, f3_params) {
  await f1.apply(this, f1_params);
  await f2.apply(self, f2_params);
  await f3.apply(self, f3_params);
}

function functionOne(p1, p2) {
  console.log("one");
  return new Promise(function(resolve,reject) {
    $.ajax({
      url: "www.url.com",
      type: "POST",
      cache: false,
      success: function(data) {
        functionFour(resolve)
      )
    });
  });
}

function functionTwo(p1, p2) {
  return new Promise(function(resolve,reject){
    setTimeout(function() {
      console.log("two");
      resolve();
    }, 2000);
  });
}

function functionThree(p1, p2) {
  return new Promise(function(resolve,reject){
    setTimeout(function() {
      console.log("three");
      resolve();
    }, 1000);
  });
}

function functionFour(callback) {
  setTimeout(function() {
    console.log("four");
    if (typeof callback == 'function') {
      callback();
    }
  }, 3000);
}
</code>```

You may have noticed that all asynchronous functions in the javascript world either accept a callback, return a promise or have some weird, non-standard mechanism of passing a callback like $.ajax() success property - that's why they exist - to allow you to sequence function calls.

Title: Execute all javascript functions in sequence

Body:
Callbacks is what allows asynchronous functions to continue processing in sequence. This is true for all languages: c++ (lambda callbacks), Java (lambda callbacks and CompletionStage), go, Python etc. - not just for javascript.

So to solve this you need to do the following:

ExecuteINsequence(functionOne, [p1, p2], functionTwo, [p1, p2], functionThree, [p1, p2]);

function ExecuteINsequence(f1, f1_params, f2, f2_params, f3, f3_params) {
  var self = this;

  f1.apply(this, f1_params,function(){
    // self is necessary in here because we are no
    // longer calling f2 in the context of ExecuteINsequence():
    f2.apply(self, f2_params,function(){
      // same reasoning for self:
      f3.apply(self, f3_params);
    });
  });
}

function functionOne(p1, p2, callback) {
  console.log("one");
  $.ajax({
      url: "www.url.com",
      type: "POST",
      cache: false,
      success: function(data) {
        functionFour(callback)
      );
    }
  });
}

function functionTwo(p1, p2,callback) {
  setTimeout(function() {
    console.log("two");
    if (typeof callback == 'function') {
      callback();
    }
  }, 2000);

}

function functionThree(p1, p2, callback) {
  setTimeout(function() {
    console.log("three");
    if (typeof callback == 'function') {
      callback();
    }
  }, 1000);
}

function functionFour(callback) {
  setTimeout(function() {
    console.log("four");
    if (typeof callback == 'function') {
      callback();
    }
  }, 3000);

}

Alternatively, if you are using promises you can use async/await:

ExecuteINsequence(functionOne, [p1, p2], functionTwo, [p1, p2], functionThree, [p1, p2]);

async function ExecuteINsequence(f1, f1_params, f2, f2_params, f3, f3_params) {
  await f1.apply(this, f1_params);
  await f2.apply(self, f2_params);
  await f3.apply(self, f3_params);
}

function functionOne(p1, p2) {
  console.log("one");
  return new Promise(function(resolve,reject) {
    $.ajax({
      url: "www.url.com",
      type: "POST",
      cache: false,
      success: function(data) {
        functionFour(resolve)
      )
    });
  });
}

function functionTwo(p1, p2) {
  return new Promise(function(resolve,reject){
    setTimeout(function() {
      console.log("two");
      resolve();
    }, 2000);
  });
}

function functionThree(p1, p2) {
  return new Promise(function(resolve,reject){
    setTimeout(function() {
      console.log("three");
      resolve();
    }, 1000);
  });
}

function functionFour(callback) {
  setTimeout(function() {
    console.log("four");
    if (typeof callback == 'function') {
      callback();
    }
  }, 3000);
}

You may have noticed that all asynchronous functions in the javascript world either accept a callback, return a promise or have some weird, non-standard mechanism of passing a callback like $.ajax() success property - that's why they exist - to allow you to sequence function calls.


问:
VBScript - Effecient Close Of Excel Workbook and Application
I am running a VBS macro in our ERP that opens a target Excel Document and populates the form with data from a workbook. The program works but the execution time is roughley 1 and a half minutes.

I placed sMsgBox's at diffrent points in the script to see where the lag was comming from, and the entire script executes in about 4 to 5 seconds, but when watching active processes in Task Manager, I can see that Execl remains open for roughley 1 and a half minutes, and when it does close, the ERP application refreshes and populates with the data.  Script is bellow

```<code>Dim WshShell
Set WshShell = CreateObject("WScript.Shell")

Dim yr
yr = InputBox("Please enter the payroll year")

Dim wk
wk = InputBox("Please enter the payroll week")

Dim path
path = "K:\Accounting\Payroll\JE\" & yr & "\Hourly\WK " & wk & "\RZU Payroll Template.xlsx"

Function FileExists(FilePath)
     Set fso = CreateObject("Scripting.FileSystemObject")
     If fso.FileExists(FilePath) Then
          FileExists=CBool(1)
     Else
          FileExists=Cbool(0)
     End If
End Function

If FileExists(path) Then
     Dim objExcel, objWorkbook, objSheet
     Set objExcel = CreateObject("Excel.Application")
     Set objWorkbook = objExcel.Workbooks.Open(path)
     Set objSheet = objExcel.ActiveWorkbook.Worksheets(3)

     Dim row
     row = 3

     Do While row <=28
          val1 = objSheet.Cells(row,2).value
          val2 = objSheet.Cells(row,4).value
          val3 = objSheet.Cells(row,5).value
          WshShell.SendKeys "{INSERT}"
          WshShell.SendKeys val1
          WshShell.SendKeys "{TAB}"
          WshShell.SendKeys val2
          WshShell.SendKeys "{TAB}"
          WshShell.SendKeys val3
          row = row + 1
     Loop

     objExcel.ActiveWorkbook.Close
     objExcel.Workbooks.Close
     objExcel.Application.Quit
     Set objExcel = Nothing
Else
     MsgBox("Your entries resulted in an invalid File Path.  Please check the file location and try again")
End If

MsgBox(val1)
</code>```

I believe I using ineficent code to close the Excel file, I would expect the application to close as soon as the command is executed but that does not seem to be the case.

Any help will be greatly appreciated

答:
You create multiple objects without disposing of them by setting them to Nothing, like fso, WshShell, objWorkbook and objSheet.
This may cause the delay you are experiencing.

Also, the script uses only Global variables (declared outside of a Function or Sub), and these variables do not go out of scope (get "Garbage Collected") until VBScript is reset or destroyed.

For that reason, I have re-written your code so everything Excel is done inside a helper function.

```<code>Option Explicit

Dim yr, wk, path, lastInsertedValue
yr = InputBox("Please enter the payroll year")
wk = InputBox("Please enter the payroll week")
path = "K:\Accounting\Payroll\JE\" & yr & "\Hourly\WK " & wk & "\RZU Payroll Template.xlsx"

Dim WshShell, fso
Set WshShell = CreateObject("WScript.Shell")
Set fso = CreateObject("Scripting.FileSystemObject")

If fso.FileExists(path) Then
    lastInsertedValue = Do_ExcelStuff(path)
    'you may check this lastInsertedValue if you like:
    'MsgBox(lastInsertedValue)
Else
    MsgBox("Your entries resulted in an invalid File Path.  Please check the file location and try again")
End If

'clean up objects
Set fso = Nothing
Set WshShell = Nothing

Function Do_ExcelStuff(ByVal path)
    'Helper function to do all Excel work using variables local to the function.
    'This means they go out of scope when the function ends and should be freed immediately.
    Dim objExcel, objWorkbook, objSheet, row, val1, val2, val3

    Set objExcel = CreateObject("Excel.Application")
    objExcel.DisplayAlerts = False

    Set objWorkbook = objExcel.Workbooks.Open(path)
    Set objSheet = objExcel.ActiveWorkbook.Worksheets(3)

    row = 3
    Do While row <= 28
        val1 = objSheet.Cells(row,2).value
        val2 = objSheet.Cells(row,4).value
        val3 = objSheet.Cells(row,5).value
        WshShell.SendKeys "{INSERT}"
        WshShell.SendKeys val1
        WshShell.SendKeys "{TAB}"
        WshShell.SendKeys val2
        WshShell.SendKeys "{TAB}"
        WshShell.SendKeys val3
        row = row + 1
    Loop

    objExcel.ActiveWorkbook.Close
    objExcel.Workbooks.Close
    objExcel.Quit
    'clean up objects
    Set objWorkbook = Nothing
    Set objSheet = Nothing
    Set objExcel = Nothing

    'return the last value inserted to prove the code did something
    Do_ExcelStuff = val1
End Function
</code>```

P.S. A very crude way of getting rid of the Excel process is to kill it right after the last

Set WshShell = Nothing statement.

It may cause data loss and the risk is all yours, but if you want to know how to do that, here's a small helper function for you:

```<code>Function KillExcel()
   On Error Resume Next

   Dim objWMIService, colProcess
   Set objWMIService = GetObject("winmgmts:{impersonationLevel=impersonate}" & "!\\.\root\cimv2")

   Set colProcess = objWMIService.ExecQuery ("Select * From Win32_Process",,48)
   For Each objProcess in colProcess
      If LCase(objProcess.Name) = "excel.exe" Then
         objWshShell.Run "TASKKILL /F /T /IM " & objProcess.Name, 0, False
         objProcess.Terminate()
      End If
   Next
End Function
</code>```

Body:
I am running a VBS macro in our ERP that opens a target Excel Document and populates the form with data from a workbook. The program works but the execution time is roughley 1 and a half minutes.

I placed sMsgBox's at diffrent points in the script to see where the lag was comming from, and the entire script executes in about 4 to 5 seconds, but when watching active processes in Task Manager, I can see that Execl remains open for roughley 1 and a half minutes, and when it does close, the ERP application refreshes and populates with the data.  Script is bellow

Dim WshShell
Set WshShell = CreateObject("WScript.Shell")

Dim yr
yr = InputBox("Please enter the payroll year")

Dim wk
wk = InputBox("Please enter the payroll week")

Dim path
path = "K:\Accounting\Payroll\JE\" & yr & "\Hourly\WK " & wk & "\RZU Payroll Template.xlsx"

Function FileExists(FilePath)
     Set fso = CreateObject("Scripting.FileSystemObject")
     If fso.FileExists(FilePath) Then
          FileExists=CBool(1)
     Else
          FileExists=Cbool(0)
     End If
End Function

If FileExists(path) Then
     Dim objExcel, objWorkbook, objSheet
     Set objExcel = CreateObject("Excel.Application")
     Set objWorkbook = objExcel.Workbooks.Open(path)
     Set objSheet = objExcel.ActiveWorkbook.Worksheets(3)

     Dim row
     row = 3

     Do While row <=28
          val1 = objSheet.Cells(row,2).value
          val2 = objSheet.Cells(row,4).value
          val3 = objSheet.Cells(row,5).value
          WshShell.SendKeys "{INSERT}"
          WshShell.SendKeys val1
          WshShell.SendKeys "{TAB}"
          WshShell.SendKeys val2
          WshShell.SendKeys "{TAB}"
          WshShell.SendKeys val3
          row = row + 1
     Loop

     objExcel.ActiveWorkbook.Close
     objExcel.Workbooks.Close
     objExcel.Application.Quit
     Set objExcel = Nothing
Else
     MsgBox("Your entries resulted in an invalid File Path.  Please check the file location and try again")
End If

MsgBox(val1)

I believe I using ineficent code to close the Excel file, I would expect the application to close as soon as the command is executed but that does not seem to be the case.

Any help will be greatly appreciated

Body:
You create multiple objects without disposing of them by setting them to Nothing, like fso, WshShell, objWorkbook and objSheet.
This may cause the delay you are experiencing.

Also, the script uses only Global variables (declared outside of a Function or Sub), and these variables do not go out of scope (get "Garbage Collected") until VBScript is reset or destroyed.

For that reason, I have re-written your code so everything Excel is done inside a helper function.

Option Explicit

Dim yr, wk, path, lastInsertedValue
yr = InputBox("Please enter the payroll year")
wk = InputBox("Please enter the payroll week")
path = "K:\Accounting\Payroll\JE\" & yr & "\Hourly\WK " & wk & "\RZU Payroll Template.xlsx"

Dim WshShell, fso
Set WshShell = CreateObject("WScript.Shell")
Set fso = CreateObject("Scripting.FileSystemObject")

If fso.FileExists(path) Then
    lastInsertedValue = Do_ExcelStuff(path)
    'you may check this lastInsertedValue if you like:
    'MsgBox(lastInsertedValue)
Else
    MsgBox("Your entries resulted in an invalid File Path.  Please check the file location and try again")
End If

'clean up objects
Set fso = Nothing
Set WshShell = Nothing

Function Do_ExcelStuff(ByVal path)
    'Helper function to do all Excel work using variables local to the function.
    'This means they go out of scope when the function ends and should be freed immediately.
    Dim objExcel, objWorkbook, objSheet, row, val1, val2, val3

    Set objExcel = CreateObject("Excel.Application")
    objExcel.DisplayAlerts = False

    Set objWorkbook = objExcel.Workbooks.Open(path)
    Set objSheet = objExcel.ActiveWorkbook.Worksheets(3)

    row = 3
    Do While row <= 28
        val1 = objSheet.Cells(row,2).value
        val2 = objSheet.Cells(row,4).value
        val3 = objSheet.Cells(row,5).value
        WshShell.SendKeys "{INSERT}"
        WshShell.SendKeys val1
        WshShell.SendKeys "{TAB}"
        WshShell.SendKeys val2
        WshShell.SendKeys "{TAB}"
        WshShell.SendKeys val3
        row = row + 1
    Loop

    objExcel.ActiveWorkbook.Close
    objExcel.Workbooks.Close
    objExcel.Quit
    'clean up objects
    Set objWorkbook = Nothing
    Set objSheet = Nothing
    Set objExcel = Nothing

    'return the last value inserted to prove the code did something
    Do_ExcelStuff = val1
End Function

P.S. A very crude way of getting rid of the Excel process is to kill it right after the last

Set WshShell = Nothing statement.

It may cause data loss and the risk is all yours, but if you want to know how to do that, here's a small helper function for you:

Function KillExcel()
   On Error Resume Next

   Dim objWMIService, colProcess
   Set objWMIService = GetObject("winmgmts:{impersonationLevel=impersonate}" & "!\\.\root\cimv2")

   Set colProcess = objWMIService.ExecQuery ("Select * From Win32_Process",,48)
   For Each objProcess in colProcess
      If LCase(objProcess.Name) = "excel.exe" Then
         objWshShell.Run "TASKKILL /F /T /IM " & objProcess.Name, 0, False
         objProcess.Terminate()
      End If
   Next
End Function


问:
Import XML file relating parent nodes number to their attributes Power query
I have the following code to import XML file and convert to table form in Excel 2016 qith Power Query.

```<code>let
    Source = Xml.Tables(File.Contents("D:\XML\file.xml")),
    Table0 = Source{0}[Table],
    #"Changed Type" = Table.TransformColumnTypes(Table0,{{"Attribute:index", Int64.Type}}),
    #"Expanded row" = Table.ExpandTableColumn(#"Changed Type", "row", {"column"}, {"row.column"}),
    #"Expanded row.column" = Table.ExpandTableColumn(#"Expanded row", "row.column", {"text"}, {"row.column.text"}),
    #"Expanded row.column.text" = Table.ExpandTableColumn(#"Expanded row.column", "row.column.text", {"Element:Text", "Attribute:fontName", "Attribute:fontSize", "Attribute:x", "Attribute:y", "Attribute:width", "Attribute:height"}, {"row.column.text.Element:Text", "row.column.text.Attribute:fontName", "row.column.text.Attribute:fontSize", "row.column.text.Attribute:x", "row.column.text.Attribute:y", "row.column.text.Attribute:width", "row.column.text.Attribute:height"})
in
    #"Expanded row.column.text"
</code>```

I'm getting almost the output I desire(Index and rest of attributes columns), but I don't know how to add the column for page, row and column corresponding to each attribute.

May someone help me with this please.

This is the output I'm getting with my code.

```<code>+-------+------------------------------------------+----------+----------+--------+--------+--------+--------+
| index | Text                                     | fontName | fontSize | x      | y      | width  | height |
+-------+------------------------------------------+----------+----------+--------+--------+--------+--------+
| 0     |                                          |          |          |        |        |        |        |
+-------+------------------------------------------+----------+----------+--------+--------+--------+--------+
| 1     | achene –(e.g., strawberry)               | Arial    | 12.0     | 121.10 | 83.42  | 71.04  | 12.00  |
+-------+------------------------------------------+----------+----------+--------+--------+--------+--------+
| 1     | capsule – (e.g., Brazil nut)             | Arial    | 12.0     | 121.10 | 124.82 | 101.07 | 12.00  |
+-------+------------------------------------------+----------+----------+--------+--------+--------+--------+
| 1     | caryopsis – (e.g., wheat)                | Arial    | 12.0     | 121.10 | 207.65 | 140.31 | 12.00  |
+-------+------------------------------------------+----------+----------+--------+--------+--------+--------+
| 2     | cypsela – (e.g., dandelion)              | Arial    | 12.0     | 85.10  | 69.62  | 24.36  | 12.00  |
+-------+------------------------------------------+----------+----------+--------+--------+--------+--------+
| 3     |                                          |          |          |        |        |        |        |
+-------+------------------------------------------+----------+----------+--------+--------+--------+--------+
| 4     | fibrous drupe – (e.g., coconut, walnut)  | Arial    | 12.0     | 276.29 | 239.45 | 95.42  | 12.00  |
+-------+------------------------------------------+----------+----------+--------+--------+--------+--------+
| 4     | follicle – (e.g., magnolia)              | Arial    | 12.0     | 121.10 | 266.81 | 229.57 | 12.00  |
+-------+------------------------------------------+----------+----------+--------+--------+--------+--------+
| 4     | legume – (e.g., bean, pea, peanut)       | Arial    | 12.0     | 353.94 | 266.81 | 155.71 | 12.00  |
+-------+------------------------------------------+----------+----------+--------+--------+--------+--------+
| 4     | loment – a type of indehiscent legume    | Arial    | 12.0     | 85.10  | 294.41 | 165.10 | 12.00  |
+-------+------------------------------------------+----------+----------+--------+--------+--------+--------+
| 4     | nut – (e.g., beech, hazelnut, oak acorn) | Arial    | 12.0     | 253.43 | 294.41 | 14.39  | 12.00  |
+-------+------------------------------------------+----------+----------+--------+--------+--------+--------+
| 4     | samara – (e.g., ash, elm, maple key)     | Arial    | 12.0     | 271.04 | 294.41 | 255.64 | 12.00  |
+-------+------------------------------------------+----------+----------+--------+--------+--------+--------+
| 4     | schizocarp – (e.g., carrot seed)         | Arial    | 12.0     | 85.10  | 501.43 | 432.97 | 12.00  |
+-------+------------------------------------------+----------+----------+--------+--------+--------+--------+
| 5     | silicle – (e.g., shepherd's purse)       | Arial    | 12.0     | 85.10  | 69.62  | 363.44 | 12.00  |
+-------+------------------------------------------+----------+----------+--------+--------+--------+--------+
| 5     | utricle – (e.g., strawberry)             | Arial    | 12.0     | 85.10  | 83.42  | 382.36 | 12.00  |
+-------+------------------------------------------+----------+----------+--------+--------+--------+--------+
| 6     |                                          |          |          |        |        |        |        |
+-------+------------------------------------------+----------+----------+--------+--------+--------+--------+
</code>```

And this is the desired output:

```<code>+------+-------+-----+--------+----------------------------------------+----------+----------+--------+--------+--------+--------+-----------+
| page | index | row | column | text                                   | fontName | fontSize | x      | y      | width  | height | fontStyle |
+------+-------+-----+--------+----------------------------------------+----------+----------+--------+--------+--------+--------+-----------+
| 0    | 0     |     |        |                                        |          |          |        |        |        |        |           |
+------+-------+-----+--------+----------------------------------------+----------+----------+--------+--------+--------+--------+-----------+
| 1    | 1     | 0   | 0      | achene                                 | Arial    | 12       | 121.1  | 83.42  | 71.04  | 12     |           |
|      |       |     |        |   –(e.g., strawberry)                  |          |          |        |        |        |        |           |
+------+-------+-----+--------+----------------------------------------+----------+----------+--------+--------+--------+--------+-----------+
| 1    | 1     | 1   | 0      | capsule                                | Arial    | 12       | 121.1  | 124.82 | 101.07 | 12     |           |
|      |       |     |        |   – (e.g., Brazil nut)                 |          |          |        |        |        |        |           |
+------+-------+-----+--------+----------------------------------------+----------+----------+--------+--------+--------+--------+-----------+
| 1    | 1     | 2   | 0      | caryopsis                              | Arial    | 12       | 121.1  | 207.65 | 140.31 | 12     |           |
|      |       |     |        |   – (e.g., wheat)                      |          |          |        |        |        |        |           |
+------+-------+-----+--------+----------------------------------------+----------+----------+--------+--------+--------+--------+-----------+
| 2    | 2     | 0   | 0      | cypsela                                | Arial    | 12       | 85.1   | 69.62  | 24.36  | 12     |           |
|      |       |     |        |   – (e.g., dandelion)                  |          |          |        |        |        |        |           |
+------+-------+-----+--------+----------------------------------------+----------+----------+--------+--------+--------+--------+-----------+
| 3    | 3     |     |        |                                        |          |          |        |        |        |        |           |
+------+-------+-----+--------+----------------------------------------+----------+----------+--------+--------+--------+--------+-----------+
| 4    | 4     | 0   | 0      | fibrous                                | Arial    | 12       | 276.29 | 239.45 | 95.42  | 12     | Bold      |
|      |       |     |        |   drupe – (e.g., coconut, walnut)      |          |          |        |        |        |        |           |
+------+-------+-----+--------+----------------------------------------+----------+----------+--------+--------+--------+--------+-----------+
| 4    | 4     | 1   | 1      | follicle                               | Arial    | 12       | 121.1  | 266.81 | 229.57 | 12     |           |
|      |       |     |        |   – (e.g., magnolia)                   |          |          |        |        |        |        |           |
+------+-------+-----+--------+----------------------------------------+----------+----------+--------+--------+--------+--------+-----------+
| 4    | 4     | 1   | 1      | legume                                 | Arial    | 12       | 353.94 | 266.81 | 155.71 | 12     |           |
|      |       |     |        |   – (e.g., bean, pea, peanut)          |          |          |        |        |        |        |           |
+------+-------+-----+--------+----------------------------------------+----------+----------+--------+--------+--------+--------+-----------+
| 4    | 4     | 2   | 2      | loment                                 | Arial    | 12       | 85.1   | 294.41 | 165.1  | 12     |           |
|      |       |     |        |   – a type of indehiscent legume       |          |          |        |        |        |        |           |
+------+-------+-----+--------+----------------------------------------+----------+----------+--------+--------+--------+--------+-----------+
| 4    | 4     | 2   | 2      | nut                                    | Arial    | 12       | 253.43 | 294.41 | 14.39  | 12     |           |
|      |       |     |        |   – (e.g., beech, hazelnut, oak acorn) |          |          |        |        |        |        |           |
+------+-------+-----+--------+----------------------------------------+----------+----------+--------+--------+--------+--------+-----------+
| 4    | 4     | 2   | 2      | samara                                 | Arial    | 12       | 271.04 | 294.41 | 255.64 | 12     |           |
|      |       |     |        |   – (e.g., ash, elm, maple key)        |          |          |        |        |        |        |           |
+------+-------+-----+--------+----------------------------------------+----------+----------+--------+--------+--------+--------+-----------+
| 4    | 4     | 3   | 0      | schizocarp                             | Arial    | 12       | 85.1   | 501.43 | 432.97 | 12     |           |
|      |       |     |        |   – (e.g., carrot seed)                |          |          |        |        |        |        |           |
+------+-------+-----+--------+----------------------------------------+----------+----------+--------+--------+--------+--------+-----------+
| 5    | 5     | 0   | 0      | silicle                                | Arial    | 12       | 85.1   | 69.62  | 363.44 | 12     |           |
|      |       |     |        |   – (e.g., shepherd's purse)           |          |          |        |        |        |        |           |
+------+-------+-----+--------+----------------------------------------+----------+----------+--------+--------+--------+--------+-----------+
| 5    | 5     | 1   | 1      | utricle                                | Arial    | 12       | 85.1   | 83.42  | 382.36 | 12     |           |
|      |       |     |        |   – (e.g., strawberry)                 |          |          |        |        |        |        |           |
+------+-------+-----+--------+----------------------------------------+----------+----------+--------+--------+--------+--------+-----------+
| 6    | 6     |     |        |                                        |          |          |        |        |        |        |           |
+------+-------+-----+--------+----------------------------------------+----------+----------+--------+--------+--------+--------+-----------+
</code>```

This is the input XML file:

```<code><?xml version="1.0" encoding="UTF-8"?>
<document>
    <page index="0"/>
    <page index="1">
        <row><column><text fontName="Arial" fontSize="12.0" x="121.10" y="83.42" width="71.04" height="12.00">achene –(e.g., strawberry)</text></column></row>
        <row><column><text fontName="Arial" fontSize="12.0" x="121.10" y="124.82" width="101.07" height="12.00">capsule – (e.g., Brazil nut)</text></column></row>
        <row><column><text fontName="Arial" fontSize="12.0" x="121.10" y="207.65" width="140.31" height="12.00">caryopsis – (e.g., wheat)</text></column></row>
    </page>
    <page index="2">
        <row><column><text fontName="Arial" fontSize="12.0" x="85.10" y="69.62" width="24.36" height="12.00">cypsela – (e.g., dandelion)</text></column></row>
    </page>
    <page index="3"/>
    <page index="4">
        <row><column><text fontName="Arial" fontSize="12.0" fontStyle="Bold" x="276.29" y="239.45" width="95.42" height="12.00">fibrous drupe – (e.g., coconut, walnut)</text></column></row>
        <row><column><text fontName="Arial" fontSize="12.0" x="121.10" y="266.81" width="229.57" height="12.00">follicle – (e.g., magnolia)</text></column>
             <column><text fontName="Arial" fontSize="12.0" x="353.94" y="266.81" width="155.71" height="12.00">legume – (e.g., bean, pea, peanut)</text></column></row>
        <row><column><text fontName="Arial" fontSize="12.0" x="85.10" y="294.41" width="165.10" height="12.00">loment – a type of indehiscent legume</text></column>
             <column><text fontName="Arial" fontSize="12.0" x="253.43" y="294.41" width="14.39" height="12.00">nut – (e.g., beech, hazelnut, oak acorn)</text></column>
             <column><text fontName="Arial" fontSize="12.0" x="271.04" y="294.41" width="255.64" height="12.00">samara – (e.g., ash, elm, maple key)</text></column></row>
        <row><column><text fontName="Arial" fontSize="12.0" x="85.10" y="501.43" width="432.97" height="12.00">schizocarp – (e.g., carrot seed)</text></column></row>
    </page>
    <page index="5">
        <row><column><text fontName="Arial" fontSize="12.0" x="85.10" y="69.62" width="363.44" height="12.00">silicle – (e.g., shepherd's purse)</text></column></row>
        <row><column><text fontName="Arial" fontSize="12.0" x="85.10" y="83.42" width="382.36" height="12.00">utricle – (e.g., strawberry)</text></column></row>
    </page>
    <page index="6"/>
</document>
</code>```

Thanks for any help

答:
Since those row and column numbers aren't part of the XML, you'll have to calculate them yourself while you drill down on the levels.

```<code>let
    Source = Xml.Tables(File.Contents("<Path to XML file>")),
    #"Expanded Table" = Table.ExpandTableColumn(Source, "Table", {"Attribute:index", "row"}, {"index", "row"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded Table",{{"index", Int64.Type}}),
    #"Expanded row" = Table.ExpandTableColumn(#"Changed Type", "row", {"column"}, {"column"}),
    #"Added Index" = Table.AddIndexColumn(#"Expanded row", "row_index", 0, 1),
    #"Added Custom" = Table.AddColumn(#"Added Index", "row", (C) => if C[column] = null then null else Table.RowCount(Table.SelectRows(#"Added Index", each [index] = C[index] and [row_index] <= C[row_index]))-1, Int64.Type),
    #"Expanded column" = Table.ExpandTableColumn(#"Added Custom", "column", {"text"}, {"text"}),
    #"Expanded text" = Table.ExpandTableColumn(#"Expanded column", "text", {"Element:Text", "Attribute:fontName", "Attribute:fontSize", "Attribute:x", "Attribute:y", "Attribute:width", "Attribute:height", "Attribute:fontStyle"}, {"Text", "fontName", "fontSize", "x", "y", "width", "height", "fontStyle"}),
    #"Added Index1" = Table.AddIndexColumn(#"Expanded text", "col_index", 0, 1),
    #"Added Custom1" = Table.AddColumn(#"Added Index1", "column", (C) => if C[row] = null then null else Table.RowCount(Table.SelectRows(#"Added Index1", each [index] = C[index] and [row] = C[row] and [col_index] <= C[col_index]))-1, Int64.Type),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom1",{"index", "Text", "fontName", "fontSize", "x", "y", "width", "height", "row", "column", "fontStyle"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Other Columns",{"index", "row", "column", "Text", "fontName", "fontSize", "fontStyle", "x", "y", "width", "height"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Reordered Columns",{{"fontName", type text}, {"fontSize", type number}, {"x", type number}, {"y", type number}, {"width", type number}, {"height", type number}, {"Text", type text}, {"fontStyle", type text}})
in
    #"Changed Type1"
</code>```

The tricky bit here is this custom column and the other analogous one:

```<code>(C) => if C[column] = null then null else
    Table.RowCount(
        Table.SelectRows(#"Added Index",
            each [index] = C[index] and [row_index] <= C[row_index]
        )
    ) - 1
</code>```

Body:
I have the following code to import XML file and convert to table form in Excel 2016 qith Power Query.

let
    Source = Xml.Tables(File.Contents("D:\XML\file.xml")),
    Table0 = Source{0}[Table],
    #"Changed Type" = Table.TransformColumnTypes(Table0,{{"Attribute:index", Int64.Type}}),
    #"Expanded row" = Table.ExpandTableColumn(#"Changed Type", "row", {"column"}, {"row.column"}),
    #"Expanded row.column" = Table.ExpandTableColumn(#"Expanded row", "row.column", {"text"}, {"row.column.text"}),
    #"Expanded row.column.text" = Table.ExpandTableColumn(#"Expanded row.column", "row.column.text", {"Element:Text", "Attribute:fontName", "Attribute:fontSize", "Attribute:x", "Attribute:y", "Attribute:width", "Attribute:height"}, {"row.column.text.Element:Text", "row.column.text.Attribute:fontName", "row.column.text.Attribute:fontSize", "row.column.text.Attribute:x", "row.column.text.Attribute:y", "row.column.text.Attribute:width", "row.column.text.Attribute:height"})
in
    #"Expanded row.column.text"

I'm getting almost the output I desire(Index and rest of attributes columns), but I don't know how to add the column for page, row and column corresponding to each attribute.

May someone help me with this please.

This is the output I'm getting with my code.

+-------+------------------------------------------+----------+----------+--------+--------+--------+--------+
| index | Text                                     | fontName | fontSize | x      | y      | width  | height |
+-------+------------------------------------------+----------+----------+--------+--------+--------+--------+
| 0     |                                          |          |          |        |        |        |        |
+-------+------------------------------------------+----------+----------+--------+--------+--------+--------+
| 1     | achene –(e.g., strawberry)               | Arial    | 12.0     | 121.10 | 83.42  | 71.04  | 12.00  |
+-------+------------------------------------------+----------+----------+--------+--------+--------+--------+
| 1     | capsule – (e.g., Brazil nut)             | Arial    | 12.0     | 121.10 | 124.82 | 101.07 | 12.00  |
+-------+------------------------------------------+----------+----------+--------+--------+--------+--------+
| 1     | caryopsis – (e.g., wheat)                | Arial    | 12.0     | 121.10 | 207.65 | 140.31 | 12.00  |
+-------+------------------------------------------+----------+----------+--------+--------+--------+--------+
| 2     | cypsela – (e.g., dandelion)              | Arial    | 12.0     | 85.10  | 69.62  | 24.36  | 12.00  |
+-------+------------------------------------------+----------+----------+--------+--------+--------+--------+
| 3     |                                          |          |          |        |        |        |        |
+-------+------------------------------------------+----------+----------+--------+--------+--------+--------+
| 4     | fibrous drupe – (e.g., coconut, walnut)  | Arial    | 12.0     | 276.29 | 239.45 | 95.42  | 12.00  |
+-------+------------------------------------------+----------+----------+--------+--------+--------+--------+
| 4     | follicle – (e.g., magnolia)              | Arial    | 12.0     | 121.10 | 266.81 | 229.57 | 12.00  |
+-------+------------------------------------------+----------+----------+--------+--------+--------+--------+
| 4     | legume – (e.g., bean, pea, peanut)       | Arial    | 12.0     | 353.94 | 266.81 | 155.71 | 12.00  |
+-------+------------------------------------------+----------+----------+--------+--------+--------+--------+
| 4     | loment – a type of indehiscent legume    | Arial    | 12.0     | 85.10  | 294.41 | 165.10 | 12.00  |
+-------+------------------------------------------+----------+----------+--------+--------+--------+--------+
| 4     | nut – (e.g., beech, hazelnut, oak acorn) | Arial    | 12.0     | 253.43 | 294.41 | 14.39  | 12.00  |
+-------+------------------------------------------+----------+----------+--------+--------+--------+--------+
| 4     | samara – (e.g., ash, elm, maple key)     | Arial    | 12.0     | 271.04 | 294.41 | 255.64 | 12.00  |
+-------+------------------------------------------+----------+----------+--------+--------+--------+--------+
| 4     | schizocarp – (e.g., carrot seed)         | Arial    | 12.0     | 85.10  | 501.43 | 432.97 | 12.00  |
+-------+------------------------------------------+----------+----------+--------+--------+--------+--------+
| 5     | silicle – (e.g., shepherd's purse)       | Arial    | 12.0     | 85.10  | 69.62  | 363.44 | 12.00  |
+-------+------------------------------------------+----------+----------+--------+--------+--------+--------+
| 5     | utricle – (e.g., strawberry)             | Arial    | 12.0     | 85.10  | 83.42  | 382.36 | 12.00  |
+-------+------------------------------------------+----------+----------+--------+--------+--------+--------+
| 6     |                                          |          |          |        |        |        |        |
+-------+------------------------------------------+----------+----------+--------+--------+--------+--------+

And this is the desired output:

+------+-------+-----+--------+----------------------------------------+----------+----------+--------+--------+--------+--------+-----------+
| page | index | row | column | text                                   | fontName | fontSize | x      | y      | width  | height | fontStyle |
+------+-------+-----+--------+----------------------------------------+----------+----------+--------+--------+--------+--------+-----------+
| 0    | 0     |     |        |                                        |          |          |        |        |        |        |           |
+------+-------+-----+--------+----------------------------------------+----------+----------+--------+--------+--------+--------+-----------+
| 1    | 1     | 0   | 0      | achene                                 | Arial    | 12       | 121.1  | 83.42  | 71.04  | 12     |           |
|      |       |     |        |   –(e.g., strawberry)                  |          |          |        |        |        |        |           |
+------+-------+-----+--------+----------------------------------------+----------+----------+--------+--------+--------+--------+-----------+
| 1    | 1     | 1   | 0      | capsule                                | Arial    | 12       | 121.1  | 124.82 | 101.07 | 12     |           |
|      |       |     |        |   – (e.g., Brazil nut)                 |          |          |        |        |        |        |           |
+------+-------+-----+--------+----------------------------------------+----------+----------+--------+--------+--------+--------+-----------+
| 1    | 1     | 2   | 0      | caryopsis                              | Arial    | 12       | 121.1  | 207.65 | 140.31 | 12     |           |
|      |       |     |        |   – (e.g., wheat)                      |          |          |        |        |        |        |           |
+------+-------+-----+--------+----------------------------------------+----------+----------+--------+--------+--------+--------+-----------+
| 2    | 2     | 0   | 0      | cypsela                                | Arial    | 12       | 85.1   | 69.62  | 24.36  | 12     |           |
|      |       |     |        |   – (e.g., dandelion)                  |          |          |        |        |        |        |           |
+------+-------+-----+--------+----------------------------------------+----------+----------+--------+--------+--------+--------+-----------+
| 3    | 3     |     |        |                                        |          |          |        |        |        |        |           |
+------+-------+-----+--------+----------------------------------------+----------+----------+--------+--------+--------+--------+-----------+
| 4    | 4     | 0   | 0      | fibrous                                | Arial    | 12       | 276.29 | 239.45 | 95.42  | 12     | Bold      |
|      |       |     |        |   drupe – (e.g., coconut, walnut)      |          |          |        |        |        |        |           |
+------+-------+-----+--------+----------------------------------------+----------+----------+--------+--------+--------+--------+-----------+
| 4    | 4     | 1   | 1      | follicle                               | Arial    | 12       | 121.1  | 266.81 | 229.57 | 12     |           |
|      |       |     |        |   – (e.g., magnolia)                   |          |          |        |        |        |        |           |
+------+-------+-----+--------+----------------------------------------+----------+----------+--------+--------+--------+--------+-----------+
| 4    | 4     | 1   | 1      | legume                                 | Arial    | 12       | 353.94 | 266.81 | 155.71 | 12     |           |
|      |       |     |        |   – (e.g., bean, pea, peanut)          |          |          |        |        |        |        |           |
+------+-------+-----+--------+----------------------------------------+----------+----------+--------+--------+--------+--------+-----------+
| 4    | 4     | 2   | 2      | loment                                 | Arial    | 12       | 85.1   | 294.41 | 165.1  | 12     |           |
|      |       |     |        |   – a type of indehiscent legume       |          |          |        |        |        |        |           |
+------+-------+-----+--------+----------------------------------------+----------+----------+--------+--------+--------+--------+-----------+
| 4    | 4     | 2   | 2      | nut                                    | Arial    | 12       | 253.43 | 294.41 | 14.39  | 12     |           |
|      |       |     |        |   – (e.g., beech, hazelnut, oak acorn) |          |          |        |        |        |        |           |
+------+-------+-----+--------+----------------------------------------+----------+----------+--------+--------+--------+--------+-----------+
| 4    | 4     | 2   | 2      | samara                                 | Arial    | 12       | 271.04 | 294.41 | 255.64 | 12     |           |
|      |       |     |        |   – (e.g., ash, elm, maple key)        |          |          |        |        |        |        |           |
+------+-------+-----+--------+----------------------------------------+----------+----------+--------+--------+--------+--------+-----------+
| 4    | 4     | 3   | 0      | schizocarp                             | Arial    | 12       | 85.1   | 501.43 | 432.97 | 12     |           |
|      |       |     |        |   – (e.g., carrot seed)                |          |          |        |        |        |        |           |
+------+-------+-----+--------+----------------------------------------+----------+----------+--------+--------+--------+--------+-----------+
| 5    | 5     | 0   | 0      | silicle                                | Arial    | 12       | 85.1   | 69.62  | 363.44 | 12     |           |
|      |       |     |        |   – (e.g., shepherd's purse)           |          |          |        |        |        |        |           |
+------+-------+-----+--------+----------------------------------------+----------+----------+--------+--------+--------+--------+-----------+
| 5    | 5     | 1   | 1      | utricle                                | Arial    | 12       | 85.1   | 83.42  | 382.36 | 12     |           |
|      |       |     |        |   – (e.g., strawberry)                 |          |          |        |        |        |        |           |
+------+-------+-----+--------+----------------------------------------+----------+----------+--------+--------+--------+--------+-----------+
| 6    | 6     |     |        |                                        |          |          |        |        |        |        |           |
+------+-------+-----+--------+----------------------------------------+----------+----------+--------+--------+--------+--------+-----------+

This is the input XML file:

<?xml version="1.0" encoding="UTF-8"?>
<document>
    <page index="0"/>
    <page index="1">
        <row><column><text fontName="Arial" fontSize="12.0" x="121.10" y="83.42" width="71.04" height="12.00">achene –(e.g., strawberry)</text></column></row>
        <row><column><text fontName="Arial" fontSize="12.0" x="121.10" y="124.82" width="101.07" height="12.00">capsule – (e.g., Brazil nut)</text></column></row>
        <row><column><text fontName="Arial" fontSize="12.0" x="121.10" y="207.65" width="140.31" height="12.00">caryopsis – (e.g., wheat)</text></column></row>
    </page>
    <page index="2">
        <row><column><text fontName="Arial" fontSize="12.0" x="85.10" y="69.62" width="24.36" height="12.00">cypsela – (e.g., dandelion)</text></column></row>
    </page>
    <page index="3"/>
    <page index="4">
        <row><column><text fontName="Arial" fontSize="12.0" fontStyle="Bold" x="276.29" y="239.45" width="95.42" height="12.00">fibrous drupe – (e.g., coconut, walnut)</text></column></row>
        <row><column><text fontName="Arial" fontSize="12.0" x="121.10" y="266.81" width="229.57" height="12.00">follicle – (e.g., magnolia)</text></column>
             <column><text fontName="Arial" fontSize="12.0" x="353.94" y="266.81" width="155.71" height="12.00">legume – (e.g., bean, pea, peanut)</text></column></row>
        <row><column><text fontName="Arial" fontSize="12.0" x="85.10" y="294.41" width="165.10" height="12.00">loment – a type of indehiscent legume</text></column>
             <column><text fontName="Arial" fontSize="12.0" x="253.43" y="294.41" width="14.39" height="12.00">nut – (e.g., beech, hazelnut, oak acorn)</text></column>
             <column><text fontName="Arial" fontSize="12.0" x="271.04" y="294.41" width="255.64" height="12.00">samara – (e.g., ash, elm, maple key)</text></column></row>
        <row><column><text fontName="Arial" fontSize="12.0" x="85.10" y="501.43" width="432.97" height="12.00">schizocarp – (e.g., carrot seed)</text></column></row>
    </page>
    <page index="5">
        <row><column><text fontName="Arial" fontSize="12.0" x="85.10" y="69.62" width="363.44" height="12.00">silicle – (e.g., shepherd's purse)</text></column></row>
        <row><column><text fontName="Arial" fontSize="12.0" x="85.10" y="83.42" width="382.36" height="12.00">utricle – (e.g., strawberry)</text></column></row>
    </page>
    <page index="6"/>
</document>

Thanks for any help

Body:
Since those row and column numbers aren't part of the XML, you'll have to calculate them yourself while you drill down on the levels.

let
    Source = Xml.Tables(File.Contents("<Path to XML file>")),
    #"Expanded Table" = Table.ExpandTableColumn(Source, "Table", {"Attribute:index", "row"}, {"index", "row"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded Table",{{"index", Int64.Type}}),
    #"Expanded row" = Table.ExpandTableColumn(#"Changed Type", "row", {"column"}, {"column"}),
    #"Added Index" = Table.AddIndexColumn(#"Expanded row", "row_index", 0, 1),
    #"Added Custom" = Table.AddColumn(#"Added Index", "row", (C) => if C[column] = null then null else Table.RowCount(Table.SelectRows(#"Added Index", each [index] = C[index] and [row_index] <= C[row_index]))-1, Int64.Type),
    #"Expanded column" = Table.ExpandTableColumn(#"Added Custom", "column", {"text"}, {"text"}),
    #"Expanded text" = Table.ExpandTableColumn(#"Expanded column", "text", {"Element:Text", "Attribute:fontName", "Attribute:fontSize", "Attribute:x", "Attribute:y", "Attribute:width", "Attribute:height", "Attribute:fontStyle"}, {"Text", "fontName", "fontSize", "x", "y", "width", "height", "fontStyle"}),
    #"Added Index1" = Table.AddIndexColumn(#"Expanded text", "col_index", 0, 1),
    #"Added Custom1" = Table.AddColumn(#"Added Index1", "column", (C) => if C[row] = null then null else Table.RowCount(Table.SelectRows(#"Added Index1", each [index] = C[index] and [row] = C[row] and [col_index] <= C[col_index]))-1, Int64.Type),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom1",{"index", "Text", "fontName", "fontSize", "x", "y", "width", "height", "row", "column", "fontStyle"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Other Columns",{"index", "row", "column", "Text", "fontName", "fontSize", "fontStyle", "x", "y", "width", "height"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Reordered Columns",{{"fontName", type text}, {"fontSize", type number}, {"x", type number}, {"y", type number}, {"width", type number}, {"height", type number}, {"Text", type text}, {"fontStyle", type text}})
in
    #"Changed Type1"

The tricky bit here is this custom column and the other analogous one:

(C) => if C[column] = null then null else
    Table.RowCount(
        Table.SelectRows(#"Added Index",
            each [index] = C[index] and [row_index] <= C[row_index]
        )
    ) - 1


问:
Calculate AVERAGEX
I would like to calculate the average of a measure (ETP). I use this formula.

```<code> Moyenne :=
SUMX (
    Personne;
    AVERAGEX (
        Lien_Contrat_SA_BP;
        CALCULATE (
            'Lien_Contrat_SA_BP'[ETP_Contractuel1];
            ALLEXCEPT ( 'Temps'; Temps[Date] )
        )
    )
)
</code>```

For the same month, I have two values.

For example :

```<code>January  1
February 1
March    1
March    0.8
</code>```

I would like to do this calculation

(1 + 1 + 0.9) / 3 and not (1 + 1 + 1 + 0.8)/4

How can I do this? Thank you for your answer.

答:
With the first column in your example called [Month] and the second column called [Value], this measure should give the avg you are looking for:

```<code>Measure =
AVERAGEX (
    SUMMARIZE ( Table1, Table1[Month], "avg", AVERAGE ( Table1[Value] ) ),
    [avg]
)
</code>```

It works like this:

The SUMMARIZE-function creates a virtual summarized table, calculating the average for each month. Like this:

```<code>Month     avg
January    1
Febuary    1
March     0.9
</code>```

The AVERAGEX function then returns the average of the values in the resulting [avg] column.

Title: Calculate AVERAGEX

Body:
I would like to calculate the average of a measure (ETP). I use this formula.

 Moyenne :=
SUMX (
    Personne;
    AVERAGEX (
        Lien_Contrat_SA_BP;
        CALCULATE (
            'Lien_Contrat_SA_BP'[ETP_Contractuel1];
            ALLEXCEPT ( 'Temps'; Temps[Date] )
        )
    )
)

For the same month, I have two values.

For example :

January  1
February 1
March    1
March    0.8

I would like to do this calculation

(1 + 1 + 0.9) / 3 and not (1 + 1 + 1 + 0.8)/4

How can I do this? Thank you for your answer.

Body:
With the first column in your example called [Month] and the second column called [Value], this measure should give the avg you are looking for:

Measure =
AVERAGEX (
    SUMMARIZE ( Table1, Table1[Month], "avg", AVERAGE ( Table1[Value] ) ),
    [avg]
)

It works like this:

The SUMMARIZE-function creates a virtual summarized table, calculating the average for each month. Like this:

Month     avg
January    1
Febuary    1
March     0.9

The AVERAGEX function then returns the average of the values in the resulting [avg] column.