topic How to split the table and create a new table based on column value without using filter in Desktop

How to split the table and create a new table based on column value without using filter

Manjarigoyal1 — Wed, 20 Jul 2022 21:32:43 GMT

I have a table with a date and site. Each week there will be new sites with that week's date. I need to split this table by Data date column so that I have a new table for each data date. Then I need to merge the tables by Site and then compare previous week to next week and create a column with values existing/drop and New. it will be rolling calulation for each week. Is there any other way to do it. Below is the screenshot of demo data.

Input File:

Output File:

Re: How to split the table and create a new table based on column value without using filter

amitchandak — Thu, 21 Jul 2022 03:46:41 GMT

Manjarigoyal1 , Not very clear , why you need break table for this week vs last week

The information you have provided is not making the problem clear to me. Can you please explain with an example.

Appreciate your Kudos.

Refer if this can help

Power BI — Week on Week and WTD
https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-Last-Week/ba-p/1051123
https://www.youtube.com/watch?v=pnAesWxYgJ8

Re: How to split the table and create a new table based on column value without using filter

Manjarigoyal1 — Thu, 21 Jul 2022 03:58:31 GMT

I need to see the history/trend week by week that how many sites are dropped from last week to this week, how many sites are still there from last week and how many sites are added this week.

Re: How to split the table and create a new table based on column value without using filter

v-xiaotang — Mon, 25 Jul 2022 10:04:41 GMT

Hi Manjarigoyal1

Thanks for reaching out to us.

>> I need to split this table by Data date column so that I have a new table for each data date. Then I need to merge the tables by Site and then compare previous week to next week and create a column with values existing/drop and New.

Is there only two columns date and site in the input file? Because in the output file, the relationship between the various columns is not obvious, so it is not clear from the picture how to split the columns in the input file.

Best Regards,

Community Support Team _Tang

If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

Re: How to split the table and create a new table based on column value without using filter

Manjarigoyal1 — Mon, 25 Jul 2022 19:48:07 GMT

There are only two columns in the original table Site and Data date. the new dates will be added each week to the table.

Re: How to split the table and create a new table based on column value without using filter

v-xiaotang — Wed, 27 Jul 2022 03:52:23 GMT

Hi Manjarigoyal1

Thanks for your reply.

>> to split the table and create a new table based on column value without using filter

usually, we use function such like

SELECTCOLUMNS function (DAX) - DAX | Microsoft Docs

SUMMARIZECOLUMNS function (DAX) - DAX | Microsoft Docs

If you need more help, please let me know.

Best Regards,

Community Support Team _Tang

If this post helps, please consider Accept it as the solution to help the other members find it more quickly.