Count of number of times value is present and create a custom column
In PowerQuery I want to add a custom column that evaluates how often a value exists and create a custom column based on the value For example, new custom column(No of Rows) should be created based on how many times value exist in Column1 and if it is greater than 1 it should display as Yes or No in the column Result Column The below screen shot is what I'm trying to create I dont want to create any group/summarized table
Automated row for averages in Power Query
Hi there, so my question is that in power query is there a way for me to get a new column that shows the average of values in a row? The number of columns will be updated on a daily basis. So, would need the average column to be updated on a daily basis. For eg: We have a table here, say just 4 columns of data as of now and 5th column being average of 4 columns on a row wise representation. What if tomorrow, another column comes in between Jan 4 & average column. I need a way for it to automate this and give me row wise data whenever a new column is added. This image is just an example from normal excel, but my data is processed in Query, of course can't show the photo since data is sensitive. Once processed within Power query, I have it loaded into a Pivot table. So, it makes crucial for me to have data set in exact same format. what can be done?
Display JSON array as column in Power Query
I'm new to power query. I'm parsing JSON. I have an array name as "categories" when I expand it using Power Query it creates three rows for each category while I just want to remain in one row and want to create 3 separate column for each category like category1,category2,category3. here is my code: let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], #"Changed Type" = Table.TransformColumnTypes(Source,{{"id", Int64.Type}, {"no", type text}, {"complete", Int64.Type}, {"json", type text}}), #"Parsed JSON" = Table.TransformColumns(#"Changed Type",{{"json", Json.Document}}), #"Expanded json" = Table.ExpandRecordColumn(#"Parsed JSON", "json", {"title", "price", "StoreName", "ratings", "merchant", "categories", "VariantB", "detailA", "detailB", "bullets", "images", "description"}, {"title", "price", "StoreName", "ratings", "merchant", "categories", "VariantB", "detailA", "detailB", "bullets", "images", "description"}) in #"Expanded json" Here is my JSON; { "title": "Braun 6-in-1 All-in-one Trimmer 3 MGK3221, Beard Trimmer for Men, Hair Clipper and Face Trimmer with Lifetime Sharp Blades, Ear & Nose Trimmer Head, 5 Attachments, Black/Volt Green, UK Two Pin Plug", "price": "£30.49", "StoreName": "Braun", "ratings": "4.5", "merchant": "Amazon", "categories": [ "Shaving & Hair Removal", "Trimmers, Clippers & Body Groomers", "Hair Clippers" ], "VariantB": { "MGK 3221": "B0842P7BRK", "MGK 3245": "B0842PDGRK" }, "detailA": { "Product Name": "Braun 6-in-1 Trimmer MGK3221", "Units": "1 count", "Brand": "Braun" }, "detailB": { "ASIN": "B0842P7BRK" }, "bullets": [ "Rechargeable all-in-one trimmer with unprecedented cutting performance vs. previous generations of Braun beard trimmers", "6-in-1 beard trimmer, face and hair trimmer for men's grooming", "Lifetime Sharp Blades and 13 length settings for utmost styling precision", "NiMH battery for 50 minutes of trimming with 10 hour charge", "Designed and engineered in Germany by Braun", "Clip hair to the length you need, simply attach the short & long hair combs, good for cutting hair from 0.5 to 21 mm" ], "images": [ "https://images-na.ssl-images-amazon.com/images/I/813c6SQbWoL._AC_SL1500_.jpg", "https://images-na.ssl-images-amazon.com/images/I/91HnJxkPEML._AC_SL1500_.jpg", "https://images-na.ssl-images-amazon.com/images/I/81fTXUsQRML._AC_SL1500_.jpg", "https://images-na.ssl-images-amazon.com/images/I/91SRO6XOChL._AC_SL1500_.jpg", "https://images-na.ssl-images-amazon.com/images/I/81-h71RecyL._AC_SL1500_.jpg", "https://images-na.ssl-images-amazon.com/images/I/81Xo4qcb4mL._AC_SL1500_.jpg", "https://images-na.ssl-images-amazon.com/images/I/8145FOZPbAL._AC_SL1500_.jpg" ], "description": "<div id=\"productDescription\" class=\"a-section a-spacing-small\"><div class=\"disclaim\">Style Name:<strong>MGK 3221</strong></div><!-- show up to 2 reviews by default --><h3>Product Description</h3><p>Master your style with the rechargeable MGK3221, 6-in-1 beard trimmer for men. With Lifetime Sharp Blades and four combs, it offers 13 lengths (0.5-21 mm), so you can easily achieve the look you want across your beard, face and hair.</p><h3>Box Contains</h3><p></p><ul><li>1 x Beard and hair trimmer</li> <li>1 x Ear and Nose trimmer attachment</li> <li>4 x comb attachments</li> <li>1 x Smart plug charger</li></ul></div>" } I want to show each object and array as a seperate column in one row. Thanks in advance for any kind of help!
