Power Query; Calculating when a certain value in numerical order is missing and creating an additional row
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ height:90px;width:728px;box-sizing:border-box;
}
Hello Fellow Super Users,
I generate a report every week that identifies demands for parts that my company makes for the current week and future weeks. The report I generate loads data from a customer file which I download, transform and load into my report using Power Query. The report shows the weeknumber for the year and the demand for that week. The problem I am facing is the customer report skips a week (week 26) and goes straight to 27 (View attached images).
What steps can I take in Power Query to calculate when a week # is missing and create an additional row for that weeknumber and fill it with "0".
I have attached images of my table generated from PQ and of the PQ table itself.
microsoft-excel microsoft-excel-2016 power-query transform
asked Jan 30 at 19:10
Matt PiccaMatt Picca
12
add a comment |
Hello Fellow Super Users,
I generate a report every week that identifies demands for parts that my company makes for the current week and future weeks. The report I generate loads data from a customer file which I download, transform and load into my report using Power Query. The report shows the weeknumber for the year and the demand for that week. The problem I am facing is the customer report skips a week (week 26) and goes straight to 27 (View attached images).
What steps can I take in Power Query to calculate when a week # is missing and create an additional row for that weeknumber and fill it with "0".
I have attached images of my table generated from PQ and of the PQ table itself.
microsoft-excel microsoft-excel-2016 power-query transform
asked Jan 30 at 19:10
Matt PiccaMatt Picca
12
Do you mean you want to add a new Row with empty data but list 26 Week in Table? If yes, I think you can create a new table with one Row you need and add to Power Query, then Append them.
– Lee
Jan 31 at 8:13
This question has been solved on Stack Overflow, thank you for your input!
– Matt Picca
Jan 31 at 17:47
Answer to my question is on the link below: stackoverflow.com/questions/54448917/…
– Matt Picca
Jan 31 at 17:48
add a comment |
Hello Fellow Super Users,
I generate a report every week that identifies demands for parts that my company makes for the current week and future weeks. The report I generate loads data from a customer file which I download, transform and load into my report using Power Query. The report shows the weeknumber for the year and the demand for that week. The problem I am facing is the customer report skips a week (week 26) and goes straight to 27 (View attached images).
What steps can I take in Power Query to calculate when a week # is missing and create an additional row for that weeknumber and fill it with "0".
I have attached images of my table generated from PQ and of the PQ table itself.
microsoft-excel microsoft-excel-2016 power-query transform
asked Jan 30 at 19:10
Matt PiccaMatt Picca
12
Hello Fellow Super Users,
I generate a report every week that identifies demands for parts that my company makes for the current week and future weeks. The report I generate loads data from a customer file which I download, transform and load into my report using Power Query. The report shows the weeknumber for the year and the demand for that week. The problem I am facing is the customer report skips a week (week 26) and goes straight to 27 (View attached images).
What steps can I take in Power Query to calculate when a week # is missing and create an additional row for that weeknumber and fill it with "0".
I have attached images of my table generated from PQ and of the PQ table itself.
microsoft-excel microsoft-excel-2016 power-query transform
microsoft-excel microsoft-excel-2016 power-query transform
asked Jan 30 at 19:10
Matt PiccaMatt Picca
12
asked Jan 30 at 19:10
Matt PiccaMatt Picca
12
edited Jan 30 at 19:33
Matt Picca
asked Jan 30 at 19:10
Matt PiccaMatt Picca
12
asked Jan 30 at 19:10
Matt PiccaMatt Picca
12
asked Jan 30 at 19:10
Matt PiccaMatt Picca
12
12
Do you mean you want to add a new Row with empty data but list 26 Week in Table? If yes, I think you can create a new table with one Row you need and add to Power Query, then Append them.
– Lee
Jan 31 at 8:13
This question has been solved on Stack Overflow, thank you for your input!
– Matt Picca
Jan 31 at 17:47
Answer to my question is on the link below: stackoverflow.com/questions/54448917/…
– Matt Picca
Jan 31 at 17:48
add a comment |
Do you mean you want to add a new Row with empty data but list 26 Week in Table? If yes, I think you can create a new table with one Row you need and add to Power Query, then Append them.
– Lee
Jan 31 at 8:13
This question has been solved on Stack Overflow, thank you for your input!
– Matt Picca
Jan 31 at 17:47
Answer to my question is on the link below: stackoverflow.com/questions/54448917/…
– Matt Picca
Jan 31 at 17:48
Do you mean you want to add a new Row with empty data but list 26 Week in Table? If yes, I think you can create a new table with one Row you need and add to Power Query, then Append them.
– Lee
Jan 31 at 8:13
Do you mean you want to add a new Row with empty data but list 26 Week in Table? If yes, I think you can create a new table with one Row you need and add to Power Query, then Append them.
– Lee
Jan 31 at 8:13
This question has been solved on Stack Overflow, thank you for your input!
– Matt Picca
Jan 31 at 17:47
This question has been solved on Stack Overflow, thank you for your input!
– Matt Picca
Jan 31 at 17:47
Answer to my question is on the link below: stackoverflow.com/questions/54448917/…
– Matt Picca
Jan 31 at 17:48
Answer to my question is on the link below: stackoverflow.com/questions/54448917/…
– Matt Picca
Jan 31 at 17:48
add a comment |
0
active
oldest
votes
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "3"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fsuperuser.com%2fquestions%2f1400217%2fpower-query-calculating-when-a-certain-value-in-numerical-order-is-missing-and%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
0
active
oldest
votes
0
active
oldest
votes
active
oldest
votes
active
oldest
votes
Thanks for contributing an answer to Super User!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fsuperuser.com%2fquestions%2f1400217%2fpower-query-calculating-when-a-certain-value-in-numerical-order-is-missing-and%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Do you mean you want to add a new Row with empty data but list 26 Week in Table? If yes, I think you can create a new table with one Row you need and add to Power Query, then Append them.
– Lee
Jan 31 at 8:13
This question has been solved on Stack Overflow, thank you for your input!
– Matt Picca
Jan 31 at 17:47
Answer to my question is on the link below: stackoverflow.com/questions/54448917/…
– Matt Picca
Jan 31 at 17:48