Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
abc_777
Post Prodigy
Post Prodigy

hi

Hi There,

 

I have attached a data set in excel file. and i want to build a stack chart like the following image.

 

Picture1.png

 

please help

thanks

1 ACCEPTED SOLUTION
v-tangjie-msft
Community Support
Community Support

Hi @abc_777 ,

 

According to your description, here are my steps you can follow as a solution.

(1) My test data is the same as yours.

(2) Click "transform data" to enter the power query editor, copy the table and then delete the extra columns, then click "Advanced Editor", copy and paste the following code (please be careful to keep your data source), and check the steps in the step column on the right.

let
    Source = Excel.Workbook(File.Contents("C:\xxxxx\data.xlsx"), null, true),
    #"CEP online Assessment Tool_Sheet" = Source{[Item="CEP online Assessment Tool",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(#"CEP online Assessment Tool_Sheet", [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Age", Int64.Type}, {"Sex", type text}, {"Profession", type text}, {"If you answered other profession, please specify", type text}, {"Educational level", type text}, {"If you answered other educational level, please specify", type text}, {"Place of work", type text}, {"Training Level", type text}, {"If you completed L2, what was the focus of training", type text}, {"Anthrax", Int64.Type}, {"CCHF", Int64.Type}, {"Brucellosis", Int64.Type}, {"Dengue", Int64.Type}, {"Infection prevention and control", Int64.Type}, {"Tularemia", Int64.Type}, {"Q Fever", Int64.Type}, {"MERS", Int64.Type}, {"Date when CEP training completed", type date}, {"Work and travel history should be included in the clinician’s assessment with the patient to help identify if a zoonotic pathogen may be the cause of illness.", type text}, {"The causative organism of CCHF is a virus?", type text}, {"The primary risk factors for Anthrax is work with animals at farms, handle animal products", type text}, {"Laboratory confirmation is critical in the early management of patients with illnesses caused by zoonotic pathogens.", type text}, {"Use of personal protective equipment (PPE) minimizes the risk of healthcare associated infections", type text}, {"CEP training was useful to me in my current role", type logical}, {"Environment, live animals, carcasses/burial sites are the primary reservoir for anthrax in Iraq", type logical}, {"Proper hand hygiene actions preventing transmission of germs to the patient and to the health care workers", type text}, {"Sharps (e.g., venipuncture needles, lancets, etc.) and specimen collection containers, etc. should be separated from other wastes", type text}, {"Use of differential diagnosis to narrow down on root cause of illness", type text}, {"More knowledge about zoonotic diseases", type text}, {"Knowledge about infection prevention and control", type text}, {"Knowledge about how to report cases with zoonotic diseases to higher levels (district/governorate, CDC Iraq)", type text}, {"Will you recommend the CEP program for your colleagues for improving their skills and practices for managing zoonotic diseases?", type text}, {"Will you recommend that you should get a higher level training on the prevention, detection and management of zoonotic cases?", type text}, {"MoH clinical guidelines are key for managing patients with exposure to various zoonotic diseases", type text}, {"I always ask patients about their travel and occupational history while going through differential diagnosis to determine the potential root cause of the illness?", type text}, {"I always access my patient’s laboratory test results to assist me in narrowing down the potential cause of the patient’s illness", type text}, {"I always use personal protective equipment (PPE) to minimize my own risk and other patients’ risk of acquiring a hospital associated infection", type text}, {"I always ask or draw blood for culture or other laboratory specimens from a patient before initiating them on treatment particularly on antibiotics?", type text}, {"I always notify a suspect or confirmed case with a zoonotic disease to the district public health office immediately?", type text}, {"I actively share knowledge that I gained during the CEP training about zoonotic pathogens and diseases among my peers and staff?", type text}, {"Proper hand washing with soap and water frequently and practicing respiratory etiquette would protect me, my peers, and other patients from acquiring pathogens in my facilities", type text}, {"Do you think the current level of preparedness in your healthcare facility is sufficient to manage zoonotic disease outbreaks?", type text}, {"Do you feel the availability of medical supplies and PPEs at your health facility are currently sufficient if a patient or multiple patients were to present with illness consistent with a potential zoonotic disease?", type text}, {"I always keep myself informed about the current epidemiological situation of zoonotic pathogens both in our region and globally?", type text}, {"I have regular conversations with my facility’s laboratory professionals about the diagnostic tools available to confirm zoonotic pathogens", type text}, {"I believe there is a need to have regular continued medical education programs/updates, specifically for managing zoonotic disease outbreaks", type text}, {"Have you or your facility seen an increase in patients suspected of zoonotic diseases?", type text}, {"How many zoonotic disease suspects in the last month?", Int64.Type}, {"What is the average number of zoonotic disease suspects per month?", Int64.Type}, {"Is the clinic staff following infection prevention and control practices (National Guideline)?", type text}, {"Does your facility hold regular discussions on the zoonotic diseases after you participated in the CEP training?", type text}, {"Have you received any mentoring or supervisory support related to zoonotic diseases from the district or governorate health offices?", type text}, {"The following tests should be done to confirm a case of CCHF", type text}, {"The following tests should be done to confirm a case of CCHF/Real time Polymerase chain reaction (PCR)", Int64.Type}, {"The following tests should be done to confirm a case of CCHF/Enzyme-linked immunosorbent assay (ELISA)", Int64.Type}, {"The following tests should be done to confirm a case of CCHF/Both", Int64.Type}, {"The following tests should be done to confirm a case of CCHF/Neither", Int64.Type}, {"Do you or other healthcare workers at this facility discard used needles in sharps safety box (leakproof, puncture proof) containers?", type text}, {"Does your facility collect specimens from suspects with zoonotic diseases and how do you handle these specimens for zoonotic pathogens?", type text}, {"Does the facility have a written plan for managing zoonotic disease outbreaks?", type text}, {"Does the facility or the district health office has a plan for managing public health emergencies during mass gatherings?", type text}, {"Based on the CEP training you received, did you feel prepared to handle the cases/patients with zoonotic diseases you saw?", type text}, {"if you answered no for the question for your level of confidence for “handling the cases/patients with zoonotic diseases” can you provide why it did not prepare you adequately?", type text}, {"How many suspects or cases of zoonotic diseases have you seen in the last three months?", type text}, {"If there was no case with a zoonotic disease identified at this facility, what will you do differently to increase detection and diagnosis of such cases? Select all that are applicable", type text}, {"Increase staff knowledge and skills of facility staff to detect and diagnose cases with zoonotic diseases", Int64.Type}, {"Increase access to laboratories that can provide confirmation of presence of a zoonotic disease", Int64.Type}, {"Improve supervision and mentoring of facility staff to continuously build skills of staff", Int64.Type}, {"Other", Int64.Type}}),
    #"Removed Other Columns" = Table.SelectColumns(#"Changed Type",{"MoH clinical guidelines are key for managing patients with exposure to various zoonotic diseases", "I always ask patients about their travel and occupational history while going through differential diagnosis to determine the potential root cause of the illness?", "I always access my patient’s laboratory test results to assist me in narrowing down the potential cause of the patient’s illness", "I always use personal protective equipment (PPE) to minimize my own risk and other patients’ risk of acquiring a hospital associated infection", "I always ask or draw blood for culture or other laboratory specimens from a patient before initiating them on treatment particularly on antibiotics?", "I always notify a suspect or confirmed case with a zoonotic disease to the district public health office immediately?", "I actively share knowledge that I gained during the CEP training about zoonotic pathogens and diseases among my peers and staff?", "Proper hand washing with soap and water frequently and practicing respiratory etiquette would protect me, my peers, and other patients from acquiring pathogens in my facilities", "Do you think the current level of preparedness in your healthcare facility is sufficient to manage zoonotic disease outbreaks?", "Do you feel the availability of medical supplies and PPEs at your health facility are currently sufficient if a patient or multiple patients were to present with illness consistent with a potential zoonotic disease?", "I always keep myself informed about the current epidemiological situation of zoonotic pathogens both in our region and globally?", "I have regular conversations with my facility’s laboratory professionals about the diagnostic tools available to confirm zoonotic pathogens", "I believe there is a need to have regular continued medical education programs/updates, specifically for managing zoonotic disease outbreaks"}),
    #"Demoted Headers" = Table.DemoteHeaders(#"Removed Other Columns"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Demoted Headers",{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}, {"Column8", type text}, {"Column9", type text}, {"Column10", type text}, {"Column11", type text}, {"Column12", type text}, {"Column13", type text}}),
    #"Transposed Table" = Table.Transpose(#"Changed Type1"),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Transposed Table", {"Column1"}, "Attribute", "Value"),
    #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Other Columns",{"Attribute"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Column1", "Type"}})
in
    #"Renamed Columns"

vtangjiemsft_0-1685415445883.pngvtangjiemsft_1-1685415462249.png

 

(3)Apply and close then we can create a  calculated column.

Count = COUNTROWS(FILTER('CEP online Assessment Tool (2)','CEP online Assessment Tool (2)'[Type]=EARLIER('CEP online Assessment Tool (2)'[Type]) ))

(4) Then the result is as follows.

vtangjiemsft_2-1685415540252.png

Best Regards,

Neeko Tang

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

View solution in original post

1 REPLY 1
v-tangjie-msft
Community Support
Community Support

Hi @abc_777 ,

 

According to your description, here are my steps you can follow as a solution.

(1) My test data is the same as yours.

(2) Click "transform data" to enter the power query editor, copy the table and then delete the extra columns, then click "Advanced Editor", copy and paste the following code (please be careful to keep your data source), and check the steps in the step column on the right.

let
    Source = Excel.Workbook(File.Contents("C:\xxxxx\data.xlsx"), null, true),
    #"CEP online Assessment Tool_Sheet" = Source{[Item="CEP online Assessment Tool",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(#"CEP online Assessment Tool_Sheet", [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Age", Int64.Type}, {"Sex", type text}, {"Profession", type text}, {"If you answered other profession, please specify", type text}, {"Educational level", type text}, {"If you answered other educational level, please specify", type text}, {"Place of work", type text}, {"Training Level", type text}, {"If you completed L2, what was the focus of training", type text}, {"Anthrax", Int64.Type}, {"CCHF", Int64.Type}, {"Brucellosis", Int64.Type}, {"Dengue", Int64.Type}, {"Infection prevention and control", Int64.Type}, {"Tularemia", Int64.Type}, {"Q Fever", Int64.Type}, {"MERS", Int64.Type}, {"Date when CEP training completed", type date}, {"Work and travel history should be included in the clinician’s assessment with the patient to help identify if a zoonotic pathogen may be the cause of illness.", type text}, {"The causative organism of CCHF is a virus?", type text}, {"The primary risk factors for Anthrax is work with animals at farms, handle animal products", type text}, {"Laboratory confirmation is critical in the early management of patients with illnesses caused by zoonotic pathogens.", type text}, {"Use of personal protective equipment (PPE) minimizes the risk of healthcare associated infections", type text}, {"CEP training was useful to me in my current role", type logical}, {"Environment, live animals, carcasses/burial sites are the primary reservoir for anthrax in Iraq", type logical}, {"Proper hand hygiene actions preventing transmission of germs to the patient and to the health care workers", type text}, {"Sharps (e.g., venipuncture needles, lancets, etc.) and specimen collection containers, etc. should be separated from other wastes", type text}, {"Use of differential diagnosis to narrow down on root cause of illness", type text}, {"More knowledge about zoonotic diseases", type text}, {"Knowledge about infection prevention and control", type text}, {"Knowledge about how to report cases with zoonotic diseases to higher levels (district/governorate, CDC Iraq)", type text}, {"Will you recommend the CEP program for your colleagues for improving their skills and practices for managing zoonotic diseases?", type text}, {"Will you recommend that you should get a higher level training on the prevention, detection and management of zoonotic cases?", type text}, {"MoH clinical guidelines are key for managing patients with exposure to various zoonotic diseases", type text}, {"I always ask patients about their travel and occupational history while going through differential diagnosis to determine the potential root cause of the illness?", type text}, {"I always access my patient’s laboratory test results to assist me in narrowing down the potential cause of the patient’s illness", type text}, {"I always use personal protective equipment (PPE) to minimize my own risk and other patients’ risk of acquiring a hospital associated infection", type text}, {"I always ask or draw blood for culture or other laboratory specimens from a patient before initiating them on treatment particularly on antibiotics?", type text}, {"I always notify a suspect or confirmed case with a zoonotic disease to the district public health office immediately?", type text}, {"I actively share knowledge that I gained during the CEP training about zoonotic pathogens and diseases among my peers and staff?", type text}, {"Proper hand washing with soap and water frequently and practicing respiratory etiquette would protect me, my peers, and other patients from acquiring pathogens in my facilities", type text}, {"Do you think the current level of preparedness in your healthcare facility is sufficient to manage zoonotic disease outbreaks?", type text}, {"Do you feel the availability of medical supplies and PPEs at your health facility are currently sufficient if a patient or multiple patients were to present with illness consistent with a potential zoonotic disease?", type text}, {"I always keep myself informed about the current epidemiological situation of zoonotic pathogens both in our region and globally?", type text}, {"I have regular conversations with my facility’s laboratory professionals about the diagnostic tools available to confirm zoonotic pathogens", type text}, {"I believe there is a need to have regular continued medical education programs/updates, specifically for managing zoonotic disease outbreaks", type text}, {"Have you or your facility seen an increase in patients suspected of zoonotic diseases?", type text}, {"How many zoonotic disease suspects in the last month?", Int64.Type}, {"What is the average number of zoonotic disease suspects per month?", Int64.Type}, {"Is the clinic staff following infection prevention and control practices (National Guideline)?", type text}, {"Does your facility hold regular discussions on the zoonotic diseases after you participated in the CEP training?", type text}, {"Have you received any mentoring or supervisory support related to zoonotic diseases from the district or governorate health offices?", type text}, {"The following tests should be done to confirm a case of CCHF", type text}, {"The following tests should be done to confirm a case of CCHF/Real time Polymerase chain reaction (PCR)", Int64.Type}, {"The following tests should be done to confirm a case of CCHF/Enzyme-linked immunosorbent assay (ELISA)", Int64.Type}, {"The following tests should be done to confirm a case of CCHF/Both", Int64.Type}, {"The following tests should be done to confirm a case of CCHF/Neither", Int64.Type}, {"Do you or other healthcare workers at this facility discard used needles in sharps safety box (leakproof, puncture proof) containers?", type text}, {"Does your facility collect specimens from suspects with zoonotic diseases and how do you handle these specimens for zoonotic pathogens?", type text}, {"Does the facility have a written plan for managing zoonotic disease outbreaks?", type text}, {"Does the facility or the district health office has a plan for managing public health emergencies during mass gatherings?", type text}, {"Based on the CEP training you received, did you feel prepared to handle the cases/patients with zoonotic diseases you saw?", type text}, {"if you answered no for the question for your level of confidence for “handling the cases/patients with zoonotic diseases” can you provide why it did not prepare you adequately?", type text}, {"How many suspects or cases of zoonotic diseases have you seen in the last three months?", type text}, {"If there was no case with a zoonotic disease identified at this facility, what will you do differently to increase detection and diagnosis of such cases? Select all that are applicable", type text}, {"Increase staff knowledge and skills of facility staff to detect and diagnose cases with zoonotic diseases", Int64.Type}, {"Increase access to laboratories that can provide confirmation of presence of a zoonotic disease", Int64.Type}, {"Improve supervision and mentoring of facility staff to continuously build skills of staff", Int64.Type}, {"Other", Int64.Type}}),
    #"Removed Other Columns" = Table.SelectColumns(#"Changed Type",{"MoH clinical guidelines are key for managing patients with exposure to various zoonotic diseases", "I always ask patients about their travel and occupational history while going through differential diagnosis to determine the potential root cause of the illness?", "I always access my patient’s laboratory test results to assist me in narrowing down the potential cause of the patient’s illness", "I always use personal protective equipment (PPE) to minimize my own risk and other patients’ risk of acquiring a hospital associated infection", "I always ask or draw blood for culture or other laboratory specimens from a patient before initiating them on treatment particularly on antibiotics?", "I always notify a suspect or confirmed case with a zoonotic disease to the district public health office immediately?", "I actively share knowledge that I gained during the CEP training about zoonotic pathogens and diseases among my peers and staff?", "Proper hand washing with soap and water frequently and practicing respiratory etiquette would protect me, my peers, and other patients from acquiring pathogens in my facilities", "Do you think the current level of preparedness in your healthcare facility is sufficient to manage zoonotic disease outbreaks?", "Do you feel the availability of medical supplies and PPEs at your health facility are currently sufficient if a patient or multiple patients were to present with illness consistent with a potential zoonotic disease?", "I always keep myself informed about the current epidemiological situation of zoonotic pathogens both in our region and globally?", "I have regular conversations with my facility’s laboratory professionals about the diagnostic tools available to confirm zoonotic pathogens", "I believe there is a need to have regular continued medical education programs/updates, specifically for managing zoonotic disease outbreaks"}),
    #"Demoted Headers" = Table.DemoteHeaders(#"Removed Other Columns"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Demoted Headers",{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}, {"Column8", type text}, {"Column9", type text}, {"Column10", type text}, {"Column11", type text}, {"Column12", type text}, {"Column13", type text}}),
    #"Transposed Table" = Table.Transpose(#"Changed Type1"),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Transposed Table", {"Column1"}, "Attribute", "Value"),
    #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Other Columns",{"Attribute"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Column1", "Type"}})
in
    #"Renamed Columns"

vtangjiemsft_0-1685415445883.pngvtangjiemsft_1-1685415462249.png

 

(3)Apply and close then we can create a  calculated column.

Count = COUNTROWS(FILTER('CEP online Assessment Tool (2)','CEP online Assessment Tool (2)'[Type]=EARLIER('CEP online Assessment Tool (2)'[Type]) ))

(4) Then the result is as follows.

vtangjiemsft_2-1685415540252.png

Best Regards,

Neeko Tang

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.