• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Cross transformation with multiple conditions

1

1shivamkushwaha

New Member
This is the file,
Each group should have only one file,
That one file output should be: Original (input row count) , Final or filtered or unique(row successCount)

if { final file is present, original input row count , final row successcount}
if else { unique file is present, original input row count , unique row successcount}
if else { filtered file is present, original input row count , filtered row successcount}
default { file input row count . file row successcount}

You will get a better picture when check the below file. (check both the sheets)
Preference is
1st final
2nd unique
3rd filtered

Thanks in advance.
 

Attachments

  • samplefile.xlsx
    25.6 KB · Views: 8
p45cal said:
Might need the .eml file too to be able to play with this…
Click to expand...
{
"[gmCiftsTelePhonyNAReviewRequest.idsl]": [
{
"fileName": "20211001_SQS_TLPHNY.txt",
"inputRowCount": "374471",
"rowSuccessCount": "374471",
"rowSkipCount": "0",
"failure": "0",
"createdDate": "2021-10-03"
},
{
"fileName": "20211001_SQS_TLPHNY_unique.csv",
"inputRowCount": "361181",
"rowSuccessCount": "326101",
"rowSkipCount": "3738",
"failure": "31342",
"createdDate": "2021-10-03"
},
{
"fileName": "20211001_SQS_TLPHNY_filtered.csv",
"inputRowCount": "368101",
"rowSuccessCount": "368101",
"rowSkipCount": "0",
"failure": "0",
"createdDate": "2021-10-03"
}
],
"[gmCiftsSPOTACSurveyRequest.idsl]": [
{
"fileName": "SPOTAC_20211003_0747.txt",
"inputRowCount": "635",
"rowSuccessCount": "635",
"rowSkipCount": "0",
"failure": "0",
"createdDate": "2021-10-03"
},
{
"fileName": "SPOTAC_20211003_0747_filtered.csv",
"inputRowCount": "600",
"rowSuccessCount": "290",
"rowSkipCount": "0",
"failure": "310",
"createdDate": "2021-10-03"
}
],
"[gmCiftsTelePhonySAIOACReviewRequest.idsl]": [
{
"fileName": "SAIO_AC_20211003_filtered.csv",
"inputRowCount": "81",
"rowSuccessCount": "8",
"rowSkipCount": "66",
"failure": "7",
"createdDate": "2021-10-03"
},
{
"fileName": "SAIO_AC_20211003.csv",
"inputRowCount": "134",
"rowSuccessCount": "134",
"rowSkipCount": "0",
"failure": "0",
"createdDate": "2021-10-03"
}
],
"[gmCiftsOBWCSurveyRequest.idsl]": [
{
"fileName": "20211002_syd_OBWC_Filtered.csv",
"inputRowCount": "538",
"rowSuccessCount": "533",
"rowSkipCount": "0",
"failure": "5",
"createdDate": "2021-10-03"
}
],
"[gmCiftsNLMACSurveyRequest.idsl]": [
{
"fileName": "NLM_AC_20211003_Filtered.csv",
"inputRowCount": "3",
"rowSuccessCount": "3",
"rowSkipCount": "0",
"failure": "0",
"createdDate": "2021-10-03"
}
],
"[gmCiftsTelePhonySAIOSRReviewRequest.idsl]": [
{
"fileName": "SAIO_SR_20211003.csv",
"inputRowCount": "236",
"rowSuccessCount": "236",
"rowSkipCount": "0",
"failure": "0",
"createdDate": "2021-10-03"
},
{
"fileName": "SAIO_SR_20211003_filtered.csv",
"inputRowCount": "151",
"rowSuccessCount": "51",
"rowSkipCount": "67",
"failure": "33",
"createdDate": "2021-10-03"
}
],
"[gmCiftsNLMSurveyRequest.idsl]": [
{
"fileName": "NLM20211003_Final.csv",
"inputRowCount": "201",
"rowSuccessCount": "130",
"rowSkipCount": "53",
"failure": "18",
"createdDate": "2021-10-03"
}
],
"[gmCiftsRoadsideReviewRequest.idsl]": [
{
"fileName": "roadside20211003045432.txt",
"inputRowCount": "875",
"rowSuccessCount": "875",
"rowSkipCount": "0",
"failure": "0",
"createdDate": "2021-10-03"
},
{
"fileName": "roadside20211003045432_Final.csv",
"inputRowCount": "758",
"rowSuccessCount": "669",
"rowSkipCount": "22",
"failure": "67",
"createdDate": "2021-10-03"
}
]
}


Hi, This is the original file , filename format is YYYYMMDDHHMMSS

Original file input row count
after we perform the filtrations, we get the row successCount
 
Take a look at the Power Query editor steps and the PQ code (M Code) for that table and the function.
I can't teach you how to do it - it would take too long.
Do an internet search for Power Query tutorial, there are loads.
Ken Puls writes well about it. See www.excelguru.ca
 
You must log in or register to reply here.
Back
Top