All Power Query posts

Re: Graph with multiple series

v-kelly-msft — Mon, 26 Apr 2021 08:00:27 GMT

Hi ，

Create a column as below:

quantity = LOOKUPVALUE('Table (2)'[Quantity],'Table (2)'[Code],'Table'[Code],0)

And you will see:

For the related .pbix file,pls see attached.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

Iterate thru table - Adding row to all rows in a table

Shkr — Mon, 26 Apr 2021 07:44:54 GMT

For each row in this table i want to add a new row and just change "belop" to "- whatever "belop" and Kontoart to "1".

I want to do this as a step in advanced editor. Can anyone help me write the code?

Re: how to format a number column

sks2701 — Mon, 26 Apr 2021 07:35:43 GMT

Hello - thank you so much for your time, however this is still not working for me . attaching a screen shot for your reference. please can you look into it.

Re: Power Query in excel- stored in SharePoint online/One drive. Data wont refresh when its is close

amitchandak — Mon, 26 Apr 2021 07:27:54 GMT

, have you already tried these steps

https://docs.microsoft.com/en-us/power-bi/connect-data/refresh-excel-file-onedrive

Re: Append 2 tables and remove dublicate rows

Icey — Mon, 26 Apr 2021 07:11:43 GMT

Hi ,

Try this:

1. Append the two tables.

2. Group the appended table based on your requirements and add a "count" column to count rows at the same time.

3. Only keep the rows which "count"=1.

4. Expand the table.

Best Regards,

Icey

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

Re: Help on Date Format conversion

V-pazhen-msft — Mon, 26 Apr 2021 07:11:33 GMT

What is the date format in the original excel file, are both date columns are the same data type and format? Can you show a screen short.

Paul Zheng _ Community Support Team

Re: Calculate Per Unit Number when Sold Totals are within Data Field

v-yingjl — Mon, 26 Apr 2021 07:04:44 GMT

Hi ,

If you want to calculate the volumes of ID for each Account number and Account Description fields, you can create a measure like this:

Count = CALCULATE ( COUNT ( 'Table'[Period ID] ), ALLEXCEPT ( 'Table', 'table'[Account Number], 'table'[Account Description] ) )

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: Python Script to fetch data from a REST API

Rosenmeyer — Mon, 26 Apr 2021 07:24:53 GMT

Hi Kelly

I managed to fix it by reinstalling anaconda3 entirely and Power BI entirely, thanks for your reply though!

Best regards,

Rasmus

Re: How to filter a column based on a column from another table?

Icey — Mon, 26 Apr 2021 06:59:58 GMT

Hi ,

You can also try this:

let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTIyVYrViVYyAjJNDcBMYyDTHCJqAmQaGkCETWHsWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Employee ID" = _t, #"Sales Value" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Employee ID", Int64.Type}, {"Sales Value", Int64.Type}}), #"Filtered Table1" = Table.SelectRows(#"Changed Type", each List.Contains(#"Table2"[Employee ID],[Employee ID])) in #"Filtered Table1"

Best Regards,

Icey

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

Re: Table Replace Value multiple condition

gvg — Mon, 26 Apr 2021 06:46:13 GMT

Hi,

How do I change this to be used in a function with column name as a variable ?

Downloading stock data from NSE India

sonm10 — Mon, 26 Apr 2021 06:34:14 GMT

Hello,

I have a requirement to load the stock data from NSE website (nseindia.com). I found that they have the option to download historical data in a csv and the url of the download link (https://www.nseindia.com/api/historical/cm/equity?symbol=HCLTECH&series=[%22EQ%22]&from=26-03-2021&to=26-04-2021&csv=true). Through the link, I can succesfully download the required data. However, I have to load the data on a daily basis so wanted to connect directly through Power BI Desktop. I tried with the web source in the Desktop but the query keeps loading till I get a "timeout" error. I tried the same url through Postman and got the result instantly. I tried playing with the headers but no luck so far. Any suggestions is appreciated.

I also found that there is a python package (nsepy) that scraps the data from NSE site. However, with the python script, the package cannot be added to the service and requires Personal Gateway(I have standard) for using the packages.

D365 online query for only a number of entities

PatriciaL — Mon, 26 Apr 2021 06:30:23 GMT

I imported numbers from D365 into Power BI (and Excel).

But I only get one entity. But we have more than 50 entities. But the query will only list the entity which I have as standard entity in D365 settings.

Our guy from IT that it was not possible to get more than one entity via D365query into Power BI.

I'm somehow not sure if this is really true. We are for sure not the only company who has more than one entity..

Anybody any idea on this?

Thanks!

Patricia

Re: Python Script to fetch data from a REST API

v-kelly-msft — Mon, 26 Apr 2021 06:17:01 GMT

Hi ，

It‘s an error from Python side.

Try to take below steps to solve it:

1. In the virtual environment:
pip install pyOpenSSL
2. Add environment variables
F:\Anaconda\envs\open3d\Library\bin
3. Restart pycharm

Or check below thread to see whether it helps:

https://stackoverflow.com/questions/54135206/requests-caused-by-sslerrorcant-connect-to-https-url-because-the-ssl-module

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

Re: Transform Invoice Data

Icey — Mon, 26 Apr 2021 06:15:14 GMT

Hi ,

Try this:

let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("pZFva8JADMa/SrjXpSTp9dq8POcGFd0fKkMovqjSF4K10qrgt1/16qashcFCCMlD+PFcLsuUREYrT9l9DQECI2M7zfIzBHSb7Ch9C+narZpqW5Xwkpeb7Rnm9bE5tPIll16mutZTye5UbdYFJIeibL7Vx50BeVKtgAPSGj5GNoVZXt85aZMivtRYfB7gMcpvJImR2CEnx517KkmHFBx2Rxz6uscihkxgx5/J03O3SHqYEqGv8T5MD5KI2FlMi/2jRfNfNodoHHtcrN1F/8KOGf0+XGBEYL54n9rXn3vevijS5kpcfgE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Number = _t, Date = _t, #"Due Date" = _t, Code = _t, Name = _t, #"Invoice Total" = _t, #"Invoice Total (Incl. Tax)" = _t]), #"Kept First Rows and Removed Columns" = Table.DemoteHeaders( Table.RemoveColumns( Table.FirstN(Source,1), {"Invoice Total", "Invoice Total (Incl. Tax)"} ) ), #"Removed Top Rows" = Table.Skip(Source,1), #"Modified Code Column" = Table.SelectRows( Table.SelectColumns( #"Removed Top Rows", {"Code"} ), each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null})) ), #"Modified Invoice Total Column" = Table.DemoteHeaders( Table.SelectRows( Table.SelectColumns( #"Removed Top Rows", {"Invoice Total"} ), each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null})) ) ), #"Modified Invoice Total (Incl. Tax) Column" = Table.DemoteHeaders( Table.SelectRows( Table.SelectColumns( #"Removed Top Rows", {"Invoice Total (Incl. Tax)"} ), each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null})) ) ), #"Transposed Table - Kept First Rows and Removed Columns" = Table.Transpose(#"Kept First Rows and Removed Columns"), #"Transposed Table - Modified Code Column" = Table.Transpose(#"Modified Code Column"), #"Transposed Table - Modified Invoice Total Column" = Table.Transpose(#"Modified Invoice Total Column"), #"Transposed Table - Modified Invoice Total (Incl. Tax) Column" = Table.Transpose(#"Modified Invoice Total (Incl. Tax) Column"), #"Combined Tables" = Table.Combine({#"Transposed Table - Kept First Rows and Removed Columns",#"Transposed Table - Modified Code Column",#"Transposed Table - Modified Invoice Total Column",#"Transposed Table - Modified Invoice Total (Incl. Tax) Column"}), #"Transposed Table" = Table.Transpose(#"Combined Tables"), #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true]), #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Number", Int64.Type}, {"Date", type date}, {"Due Date", type date}, {"Code", type text}, {"Name", type text}, {"Invoice Items", type text}, {"Invoice Total", Int64.Type}, {"Invoice Total (Incl. Tax)", type number}}) in #"Changed Type"

Best Regards,

Icey

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

Re: Filter multiple sheets with different data by the filename

klausvm — Mon, 26 Apr 2021 06:06:23 GMT

Anybody who can help me with a solution?

Thanks in advance

Re: How to reference table[column] as variables in a function

gvg — Mon, 26 Apr 2021 05:26:24 GMT

Yep, that works. Thank you !

Re: Expand column with functions in each row

v-yingjl — Mon, 26 Apr 2021 03:01:44 GMT

Hi ,

As far as I know, function type needs specific parameters to get the query result so it would not return multiple results at the same time.

About expanding columns in power query, you can refer this article:

Work with a List, Record, or Table structured column (Power Query)

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Power Query in excel- stored in SharePoint online/One drive. Data wont refresh when its is closed.

skipper — Mon, 26 Apr 2021 01:12:10 GMT

I am having a problem having query data coming to Excel. It is API data flowing to Excel. Using power Query data is getting loaded to excel.

I have to manually open the file to get this data refresh.

I have tried Refreshing it using Microsoft power Automate but it did not work.

i have tried schedule refresh in excel and it's not working.

How do I refresh data in excel without opening it?

This data gets updated to the Power BI dataset. Not using PQ for the dataset directly because I cannot hold historical data.

Re: RLS on database level with Direct Query

lbendlin — Mon, 26 Apr 2021 00:18:58 GMT

Why would you need that? The database already knows which user is running the direct query?

Row-level security (RLS) with Power BI - Power BI | Microsoft Docs

Last FAQ.

Re: Load data from Intercom

hasnaa — Sun, 25 Apr 2021 21:56:23 GMT

I did not succeed in obtaining API key that's why I am blocked
I would like to know how to do this with an access token

Re: How to reference table[column] as variables in a function

CNENFRNL — Sun, 25 Apr 2021 20:42:43 GMT

, you may want to try

= (myTable as table, myColumn as text) => let #"Replace Value" = Table.ReplaceValue( myTable, "some text", Table.ToList(Table.SelectColumns(myTable, myColumn)){1} & " ccc", Replacer.ReplaceValue, {myColumn}) in #"Replace Value"

How to reference table[column] as variables in a function

gvg — Sun, 25 Apr 2021 19:18:51 GMT

Hi folks,

I have a table with very many columns. I am looking to build a function (fChange) that can be called on any column. It looks like this:

= (myTable as table, myColumn as text) => let #"Replace Value" = Table.ReplaceValue( myTable, "some text", myTable[myColumn]{1} & " ccc", // change to text on row 2 and " ccc" Replacer.ReplaceValue, {myColumn}) in #"Replace Value"

The function is supposed to be called using :

=fChange(TableFromPreviousStep, "Column78")

myTable[myColumn]{1} reference in line 2 is not working. Power Query interprets myColumn not as variable, but as the column name "myColumn". I need to have variable column name there so that I can call the function on any column. What is correct syntax to reference a column in a variable in "M" function? Failed to google find answer on the net.

Re: Good practice for splitting long text strings

crossover — Sun, 25 Apr 2021 16:28:09 GMT

Thanks, I wasn't able to implement this directly, but was able to pick up some methods.

Re: Multiple datasources and connections on API

Jean-Christophe — Sun, 25 Apr 2021 16:27:29 GMT

Is it now possible to do it in April 2021 ?

Another question : Wich products have you found that makes it possible ?

Re: Excel cell as input for parameter

StefanoGrimaldi — Sun, 25 Apr 2021 16:18:45 GMT

hey,

you need to convert the query column with the wanted parameter to list, go to tranform tab and select the wanted column as parameter and convert to list.

if this helped please give some kudos and mark as solution for others to find

Re: how to format a number column

mahoneypat — Sun, 25 Apr 2021 13:35:04 GMT

Ideally, you would use Custom Format Strings to do this, but it does not appear to work the same as Excel when adding a condition. For example, this custom format string works in Excel but not in Power BI Desktop.

[<100000000]#,,.0"M";#,,,.0"B"

However, you can write a DAX expression with an IF and the FORMAT function to get the same result. Don't add this as a column to your table (since it returns text), but use it in a Measure once all your calculation is done to return the result in the desired format.

NumberFormat =
VAR thisnumber =
    MIN ( CustomNumber[Formatted] )
RETURN
    IF (
        thisnumber < 1000000000,
        FORMAT ( thisnumber, "###,,.0M" ),
        FORMAT ( thisnumber, "###,,,.0B" )
    )

Pat

Re: Convert the below dax to m query

Fowmy — Sun, 25 Apr 2021 10:47:05 GMT

You try the following formula as a new custom column in PQ:

=if [Amount on Hold] <> null and Number.Round([Amount on Hold]+[AP],1) = 0 then "Payments on Hold" else null

Convert the below dax to m query

AnuTomy — Sun, 25 Apr 2021 08:49:45 GMT

https://drive.google.com/file/d/1nv4wk6uv8ZfN45kJzlyaBCKu5kYbKmtl/view?usp=sharing

how to convert the below dax to m query.

Flag PH =

IF (

NOT (

ISBLANK ( 'Table A'[Amount on Hold] )

IF (

ROUND( ('Table A'[AP ] + 'Table A'[Amount on Hold]),1) = 0,

"Payments on Hold"

)

I am fairly new to m query, can some one help with this.

Re: Good practice for splitting long text strings

Rocco_sprmnt21 — Sun, 25 Apr 2021 05:12:02 GMT

a version that takes into account, if I understand what you mean, the limit of 70 characters for the first three columns.

Re: Good practice for splitting long text strings

Rocco_sprmnt21 — Sun, 25 Apr 2021 05:03:54 GMT

here you can find all the functions available to act on the texts

I propose a draft resolution. See if it comes close to what you are looking for.

Re: Incremental Refresh With Data Flow And Power BI Per User

StefanoGrimaldi — Sun, 25 Apr 2021 00:52:33 GMT

hey, it should work fine for PPU lincence, but the dataflow need to be in a primiun asigned workspace to use that option on it,

here you can get some documentation on it: https://docs.microsoft.com/en-us/power-query/dataflows/incremental-refresh

if this helped give some kudos, and if solved your problem please mark as solved for others to find.

Re: Good practice for splitting long text strings

Jakinta — Sun, 25 Apr 2021 00:56:07 GMT

Hello,

try this.

I have added paramater NumOfChar so you can input desired number of characters to split.

let Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content], #"Added Custom" = Table.AddColumn(Source, "Custom", each Text.PositionOfAny([Column1],{" "},Occurrence.All )), Custom = #"Added Custom"{0}[Custom], #"Converted to Table" = Table.FromList(Custom, Splitter.SplitByNothing(), null, null, ExtraValues.Error), #"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Column1", Int64.Type}}), #"SPLIT (parameter)" = Table.AddColumn(#"Changed Type", "Custom", each if [Column1] < NumOfCh then 0 else if ([Column1] >= NumOfCh and [Column1] < NumOfCh*2) then 1 else if ([Column1] >= NumOfCh*2 and [Column1] < NumOfCh*3) then 2 else if [Column1] >= NumOfCh*3 then 3 else null), #"Grouped Rows" = Table.Group(#"SPLIT (parameter)", {"Custom"}, {{"Gr", each _, type table [Column1=nullable number, Custom=number]}}), #"Added Custom2" = Table.AddColumn(#"Grouped Rows", "Custom.1", each Table.AddIndexColumn([Gr], "Index")), #"Added Custom3" = Table.AddColumn(#"Added Custom2", "Position", each [Gr][Column1]{0}), #"Removed Columns" = Table.RemoveColumns(#"Added Custom3",{"Custom", "Gr", "Custom.1"}), Positions = List.Combine ({{0}, List.RemoveFirstN(#"Removed Columns"[Position], 1)}), FINAL = Table.SplitColumn(Source, "Column1", Splitter.SplitTextByPositions(Positions) ) in FINAL

Good practice for splitting long text strings

crossover — Sat, 24 Apr 2021 19:45:40 GMT

I have a column [text1] with strings of various lenghts anywhere between 10 to 300+ characters. I need to break this text down to 4 columns - knowing that a single column fits up to 70 characters and line breaking should be done nicely with a space (e.g. not to break up words - this is where my M gets annoyingly extensive). The fourth column can contain whatever is left from the broken down string.

While I can eventually get it done, the reality is that I have 2 columns that need this and my M syntax got really long and I wanted to check if there's a better alternative to Split column at 70 characters + split again at right-most space and do A LOT of replacing and custom columns.

Is there some good practice or M text manipulation functions that are well suited for this task? A minor example below of the expected outcome by splitting up to 20 characters per column. Any relevant blog posts or M functions that might be useful in this case? No good hits so far when googling around aside of basic split-by-delimiter stuff. Feels like someone must have surely tackled this issue before...

Incremental Refresh With Data Flow And Power BI Per User

JoswinPowerBi — Sat, 24 Apr 2021 19:13:15 GMT

Can you help me

I did a conection with data flow to SQL Server and i want to create incremental refresh but i have Power Bi Per User
My cuestion is, i need Power BI Premium for create incremental refresh?
With Power BI Per User I can not use incremental refresh, Why?

Re: Data maniplulation after grouping

DDON — Sat, 24 Apr 2021 18:53:50 GMT

Perfect. Thanks for your time on that. I think I understand the syntax of the solution apart from the "_". If you have a moment could you explain what that is up to or point me towards the documentation.

Thanks again

Re: Filter multiple sheets with different data by the filename

klausvm — Sat, 24 Apr 2021 17:27:31 GMT

Thanks Downy. I know that much.

I want to be able to filter the different sheets in the report by the date of the files they come from. There is no date columns in the files, so there is no relationship between the different sheets and files.

Do you know what I mean?

Excel cell as input for parameter

Tom83 — Sat, 24 Apr 2021 16:52:23 GMT

Hi guys,

I'm trying to set up a parameter in Power Query based on an Excel named cell called YTD_date. The reason I'm using a parameter is because this one can be used to add a conditional column to my dataset. This is what I've done up so far:

Added the cell to PowerQuery:

let
Source = Excel.CurrentWorkbook(){[Name="YTD_date"]}[Content],
#"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"31-12-2021", type date}})
in
#"Changed Type"

Now I want to setup the Parameter but it doens't allow me to select the query, any idea why? When creating a new query it's shown but not giving the excel balue. Is this the right way or would there be an easier solution to fix this? I just want to use an input cell to use in a conditional column to calculate the amounts YTD on record level, I don't want to filter rows based on this cell-value because I need the other rows as well.

Revenue Recognition Automation

ChavdarG — Sat, 24 Apr 2021 12:19:01 GMT

Dear all,

I have hit a wall with this one and need help from the community.

Example file here: https://drive.google.com/file/d/17mOH9IoM2y_l89wcaN04KdSED5wLo695/view?usp=sharing

The idea is generally simple: once you ship a certain quantity of goods, not all are immediately recognized as sales. Some are in transit for the current month and some are recognized in the current month (which were in transit in previous months).

What is needed is that based on the available fact and dimension tables to:

- where I am stuck: get the final quantity sold per period (shipped - in transit + recognized as sales)

- next step if possible: get the final revenue per period. Revenue of shipped - revenue in transit (based on the average per unit x quantity in transit) + revenue recognized (based on the average per unit x quantity recognized)

Why am I stuck on step 1:

-I got to a point where I get a certain quantity but since it is based on the fact table data, if there are sales in transit from previous periods (such as CC 62037, which was in transit end 2019 and sold in Jan 2020), it does not appear in my list

- the solution I see is to add to my formula for Final_Volumes also the volumes of CCs not in the fact table list but in the SiT list

- However, both lists contain duplicate values and I cannot do it

The end result for January in this example is Final Volumes of 404,742:

I'd also accept recommendation of different data modelling, if more optimal.

Let me know if further clarifications are needed.

Thank you,

Chavdar

Re: Filter multiple sheets with different data by the filename

Fowmy — Sat, 24 Apr 2021 12:18:04 GMT

When you use Get Data > Folder option to import multiple files, you get the following screen which allows you to filter the files on the dates you require.

Re: Data maniplulation after grouping

Rocco_sprmnt21 — Sat, 24 Apr 2021 11:44:59 GMT

try using this M-expression

then expand column records:

Filter multiple sheets with different data by the filename

klausvm — Sat, 24 Apr 2021 09:00:29 GMT

Hi there

I'm wondering if its possible to load a bunch of Excel files from a folder and filter them by the date created (or the date thats part of the file name).

All the Excel files are the same, but they contain multiple sheets with different columns. Not one sheet is the same in each file.

So I would be like to add the different sheets to the report and use the date from the file names to filter the data.

Is this doable?

Thanks alot in advance,

Klaus

Re: Data maniplulation after grouping

DDON — Sat, 24 Apr 2021 06:11:48 GMT

Thanks for your reply, as far as I can tell the Group GUI only allows operations on one column. In scenario 1 I am not really interested in the max of R but which V is on the same row as th max of R.

Sorry if I am missing something in your reply

Re: Data maniplulation after grouping

Rocco_sprmnt21 — Sat, 24 Apr 2021 05:36:32 GMT

you should group by on key using UI (for example) in this way:

Re: How to get a custom values from a grouped table?

watkinnc — Sat, 24 Apr 2021 01:13:29 GMT

Before grouping by city, you could add a custom column of 1 for if ends with"X" and 0 for if ends with "Y". The when you group, use the sum aggregation for the number column. If the grouped number column is > 0 then "With Partners" else "Prospects".

So before grouping:

WithOrWithout = Table.AddColumn(TableName, "Partners", each if Text.EndsWith([Data], "X") then 1 else 0, type number))

Then group on City, and use Sum to add the Partners column. Then you can add a Status column:

Table.AddColumn(GroupedTable, "Status", each if [Partners] > 0 then "With Partners" else "Prospects")

That should work!

--Nate

Re: how to format a number column

watkinnc — Fri, 23 Apr 2021 22:56:24 GMT

I bet Column From Examples would tackle that with ease...

Re: Dynamic column choice if null for index needed

watkinnc — Fri, 23 Apr 2021 22:39:08 GMT

You can use the null coalescing operator:

Table.AddColumn(TableName, "Index", each [Column1]??[Column2]??[Column3]??[Column4]??"Default Value")

Which is if Column1 =null then Column2 unless Column 2 is null then Column3 unless Column3 is null then Column4 unless Column4 is null then use Default Value.

--Nate

Re: Seeing my database's structure with Power Query

watkinnc — Fri, 23 Apr 2021 22:27:36 GMT

I would add a column of the column names of each table in the dB. PQ is actually THE BEST way to get an idea of what's in the database. Once you have your list of Db tables:

NewColumn = Table.AddColumn(LastStepName, "Columns", each Table.ColumnNames(_[DataTableName]))

This will list the columns in each table, right next to its table name. When I was new, I did this all the time.

--Nate

Re: Help on Date Format conversion

watkinnc — Fri, 23 Apr 2021 22:18:36 GMT

You need to convert the Date Column to Date format before doing Date.ToText. Right now it looks like you are performing a Date Operation on a text column.
--Nate

Re: Data maniplulation after grouping

watkinnc — Fri, 23 Apr 2021 22:12:22 GMT

You can just add a MAX operation on the Value column right from the Group function GUI. Also add an All Rows column. That should give you what you are looking for. You'll have what you need to then add the average column.

--Nate

Data maniplulation after grouping

DDON — Fri, 23 Apr 2021 20:04:23 GMT

Happy Friday!

I am trying to extract data specific for each group from one column in a table based on finding the maximum value in another column.

I have the following setup

Key = column by which data is to be grouped

Reference = The column I wish to use to identify/manipulate data

Value = The values I want to output

Scenario 1

I wish to find the corresponding value for the maximum reference within a group:

Scenario 2

(I may be pushing my luck here!)

I wish use the numbers in the Reference column to perform an operation upon the value column - here it is Average(Reference x Value). I am less fussed about the exact formulation here but the method I can apply

I would be hugely greatful for any ideas/solutions

Thanks!

Re: box.com

YunNC — Fri, 23 Apr 2021 16:51:51 GMT

Still same error as of today 4/23. Details: "The downloaded data is HTML, which isn't the expected type. The URL may be wrong or you might not have provided the right credentials to the server."

Re: Calculate Per Unit Number when Sold Totals are within Data Field

triaa21 — Fri, 23 Apr 2021 16:09:33 GMT

Unfortunately, that is not what I'm looking for. I have the P&L built in the format we want, with revenues, expenses, etc. I need to find a way to pull out the Volume from the data, and then divide the revenues/expenses by that total volume number. Any thoughts on how to do that?

Re: Help on Date Format conversion

ovetteabejuela — Fri, 23 Apr 2021 15:46:00 GMT

Unfortunately...

and my Regional settings is fine:

Re: Calculate Per Unit Number when Sold Totals are within Data Field

daxer — Fri, 23 Apr 2021 15:29:41 GMT

Maybe this would help: https://youtu.be/IGCIsCC5iXQ?

Re: Connect online CSV that automatically downloads to computer. No link available.

daxer — Fri, 23 Apr 2021 15:26:09 GMT

The file you're talking about comes from "blob:https://directory.house.gov/6a0927c6-7952-4518-b81b-968711192bc8". No way to get a direct link to it. Sorry. But I guess you could use a bit of Python (straight from Power Query) to download the file and then load it in.

Re: Incorrect syntax near the keyword 'SET'. Incorrect syntax near ')'

daxer — Fri, 23 Apr 2021 15:19:05 GMT

Why "set quoted identifier off"? Why "OPENQUERY"? Why not get the data directly from SQL Server? If you really need to use such a long-winded way... why not try to use EXEC AT?

Cleaning Data

tomshaw83 — Fri, 23 Apr 2021 15:07:20 GMT

Hi,

I am looking to reshape some messy data from a PDF table as in the example below, to create the table below that.

I can perform this transition (i think) with many, many, many applied steps. Though if there are specific techniques that produce a more efficient solution I would be keen to see them, or if anyone knows of any resources online that I can research into this I would be keen to upskill.

	Header 1	Header 2		Header 3
	Fact 1 Value 1	Fact 2 Value 1		Fact 3 Value 1
Header 4	Fact 4 Value 1		Header 5	Fact 5 Value 1	Header 6	Fact 6 Value 1	Header 7	Fact 7 Value 1

Header 1	Header 2	Header 3	Header 4	Header 5	Header 6	Header 7
Fact 1 Value	Fact 2 Value	Fact 3 Value	Fact 4 Value	Fact 5 Value	Fact 6 Value	Fact 7 Value

Thank you

Tom

Re: How to get a custom values from a grouped table?

massotebernoull — Fri, 23 Apr 2021 15:19:54 GMT

Hi , I really appreciate your help, but I didn't specified the table in a right way. Sorry about that.

My goal is to create a slicer that can show cities that have companies using my product and cities that don't have.

The best way to do this is matching two columns from a factual table: a column of Cities and a column of companies that already use my product and companies that could use it classified as "prospect" and "partner". Like this:

This way, I have to create a dimension table with a column where:

- If the city has partners, I would like to show something like "with partners" or "True";

- And if the city has just prospects, show "without partners yet" or "False"...

To get a table with unique values:

After a grouped by city code and city, it appeared a column with a table in each row and after that I couldn't get the "city with" column. Is Group By the best way to get this table with unique values?

I tried to adapt your script, but it didn't worked. Is there another way to do this?

Re: Split up addresses

NewbieJono — Fri, 23 Apr 2021 14:48:56 GMT

sorry, to make this more complaicated some address have 5 rows!

the postcode seem to be consitantly in captials if this is any help

Re: Split up addresses

CNENFRNL — Fri, 23 Apr 2021 14:39:12 GMT

, simple enough as every complete address consists of 4 rows,

let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WckxJKUotLlbIycxLVTBUitVBEzICC4Xkl+eBGcGJloYFKZjKaKmzIL+4JDk/JVUpNhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Address = _t]), Custom1 = Table.FromRows(List.Split(Source[Address],4)) in Custom1

Split up addresses

NewbieJono — Fri, 23 Apr 2021 14:03:50 GMT

i have a list of address in excel in this format

Address line 1

Address line 2

Town

Sa91pd

Address line 1

Address line 2

Town

Sa91pd

Address line 1

Address line 2

Town

postcode

the list is all in one coloumn and has multiple address. is there a method to have each address on one line with mutiple coloumns?

Query Dynamics CRM PartyList

Gerald23 — Fri, 23 Apr 2021 13:02:16 GMT

Hi Everyone,

Anyone has experience with getting data from Dynamics CRM from PartyList type fields? I try to get phone call information into power bi but i can't get the data from the To and From field because these are PartyList fields.

This is the query i use and it works but it only returns Null values for these fields.

let GetResults = (z as text, x as number) => let S = Json.Document(Web.Contents(ServiceRootURL, [RelativePath="/phonecalls", Headers=[Prefer="odata.include-annotations=*"],Query=[fetchXml=" <fetch distinct=""True"" page=""" & Text.From(x) & """ paging-cookie=""" & z & """> <entity name=""phonecall""> <all-attributes /> <link-entity name=""activityparty"" from=""activityid"" to=""activityid"" link-type=""inner"" alias=""ad""><filter type=""and""><condition attribute=""participationtypemask"" operator=""in""><value>1</value><value>2</value></condition></filter></link-entity> </entity> </fetch>"]])), P = try Xml.Document(S[#"@Microsoft.Dynamics.CRM.fetchxmlpagingcookie"]) otherwise null, R = if P <> null then List.Combine({S[value],@GetResults(Text.Replace(Text.Replace(Text.Replace(Uri.Parts("http://a.b?d=" & Uri.Parts("http://a.b?d=" & P{0}[Attributes]{1}[Value])[Query][d])[Query][d], ">", ">"), "<", "<"), """", """), x + 1)}) else S[value] in R, ResultsList = GetResults("", 1), ResultsTable = if List.IsEmpty(ResultsList) then #table( type table[ #"Subject"= text, #"Activity Status (statecode)"= text, #"Activity Status"= text, #"Priority (prioritycode)"= text, #"Priority"= text, #"Due"= text, #"Created By (createdby)"= text, #"Created By"= text, #"Regarding (regardingobjectid)"= text, #"Regarding"= text, #"activityid"= text, #"Call To (to)"= text, #"Call To"= text, #"Call From (from)"= text, #"Call From"= text ],{}) else #"Converted to Table", #"Converted to Table" = Table.FromList(ResultsList, Splitter.SplitByNothing(), null, null, ExtraValues.Error), #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", { "subject", "statecode", "statecode@OData.Community.Display.V1.FormattedValue", "prioritycode", "prioritycode@OData.Community.Display.V1.FormattedValue", "scheduledend@OData.Community.Display.V1.FormattedValue", "_createdby_value", "_createdby_value@OData.Community.Display.V1.FormattedValue", "_regardingobjectid_value", "_regardingobjectid_value@OData.Community.Display.V1.FormattedValue", "activityid", "_to_value", "_to_value@OData.Community.Display.V1.FormattedValue", "_from_value", "_from_value@OData.Community.Display.V1.FormattedValue" }, { "subject", "statecode", "statecode@OData.Community.Display.V1.FormattedValue", "prioritycode", "prioritycode@OData.Community.Display.V1.FormattedValue", "scheduledend@OData.Community.Display.V1.FormattedValue", "_createdby_value", "_createdby_value@OData.Community.Display.V1.FormattedValue", "_regardingobjectid_value", "_regardingobjectid_value@OData.Community.Display.V1.FormattedValue", "activityid", "_to_value", "_to_value@OData.Community.Display.V1.FormattedValue", "_from_value", "_from_value@OData.Community.Display.V1.FormattedValue" }), #"Renamed Columns" = Table.RenameColumns(#"Expanded Column1", { {"subject", "Subject"}, {"statecode", "Activity Status (statecode)"}, {"statecode@OData.Community.Display.V1.FormattedValue", "Activity Status"}, {"prioritycode", "Priority (prioritycode)"}, {"prioritycode@OData.Community.Display.V1.FormattedValue", "Priority"}, {"scheduledend@OData.Community.Display.V1.FormattedValue", "Due"}, {"_createdby_value", "Created By (createdby)"}, {"_createdby_value@OData.Community.Display.V1.FormattedValue", "Created By"}, {"_regardingobjectid_value", "Regarding (regardingobjectid)"}, {"_regardingobjectid_value@OData.Community.Display.V1.FormattedValue", "Regarding"}, {"activityid", "activityid"}, {"_to_value", "Call To (to)"}, {"_to_value@OData.Community.Display.V1.FormattedValue", "Call To"}, {"_from_value", "Call From (from)"}, {"_from_value@OData.Community.Display.V1.FormattedValue", "Call From"} }), #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns", { {"Subject", type text}, {"Activity Status (statecode)", type text}, {"Activity Status", type text}, {"Priority (prioritycode)", type text}, {"Priority", type text}, {"Due", type datetime}, {"Created By (createdby)", type text}, {"Created By", type text}, {"Regarding (regardingobjectid)", type text}, {"Regarding", type text}, {"activityid", type text}, {"Call To (to)", type text}, {"Call To", type text}, {"Call From (from)", type text}, {"Call From", type text} }) , #"Result" = if List.IsEmpty(ResultsList) then ResultsTable else #"Changed Type" in #"Result"

RLS on database level with Direct Query

szymon_zabiello — Fri, 23 Apr 2021 12:38:48 GMT

Here is the scenario.
Source: Teradata
Connection mode in Power BI: Direct
Requirement: Identify the user logged in, by USERPRINCIPALNAME() function and pass its value to the direct query, in order to filter the data.

Ideal solution: Pass the value of measure User_Id = USERPRINCIPALNAME() to Power Query parameter, so that it would go to my WHERE clause as a parameter and filter the data.

So far, I did not find a solution. Is it even possible?

Re: Count Number of times a value in a column appears in another column in the same table. (DAX)

rptmaxer — Fri, 23 Apr 2021 12:04:11 GMT

@AIB

Thank you. I ignored intellisense and just kept typing, It works perfectly! Thank you,maybe my computer was running slow, or maybe I am!

Please could I ask a followup question?

My data now looks like this:

I have added an aditional column where 0 occurs (Group)

Project List A Project List B Count Group
Project 1 Porject 1 1
Project 2 Project 2 2
Project 2 Project 4 2
Project 3 Project 2 0 A

Is it possible to:

Add another column (Count 2) and then count how many times Projects in Project List A (excluding the project marked A) appear in the entire list of projects in Project List B?

In plain english:

Re-run the count but exclude just Project 3.

Re: Formula firewall error - Query referenced to replace parameter

Jimmy801 — Fri, 23 Apr 2021 11:49:28 GMT

Hello

probably this is coming from your parameters Storage_path and File1. Does the first parameter end with an "\"? otherwise the complete path is not valid.

Jimmy

Re: Load data from Intercom

mahoneypat — Fri, 23 Apr 2021 11:46:52 GMT

Looks like they have a REST API. If so, this video may help get your started on getting data from REST APIs.

REST API (intercom.com)

Power BI - Tales From The Front - REST APIs - YouTube

Pat

Re: Append 2 tables and remove dublicate rows

deboec — Fri, 23 Apr 2021 11:43:42 GMT

If I use this sintax the blank query basically only appends the two queries.

Re: Append 2 tables and remove dublicate rows

CNENFRNL — Fri, 23 Apr 2021 11:33:39 GMT

, you might want to create a blank query

= Table.Distinct(#"query 1" & #"query 2")

Re: Seeing my database's structure with Power Query

CNENFRNL — Fri, 23 Apr 2021 11:27:49 GMT

, you're barking up the wrong tree. Power Query isn't a db management software; on the contrary, ask db admin for data dictionary or something similar.

Append 2 tables and remove dublicate rows

deboec — Fri, 23 Apr 2021 10:51:27 GMT

Hello guys,

I have two queries in my Excel file via connection.

Both queries are connected to tables with an identical structure (columns).

Only some of the rows are different.

I want to append query 1 with query 2 and only keep the rows which are unique.
(So not only removce duplicates but also remove all rows that are not unique).

Query 1 and query 2 both have an index column.

Load data from Intercom

hasnaa — Fri, 23 Apr 2021 10:39:49 GMT

Hi,

Does anyone have any experience with loading data from Intercom.io through Power BI?

I have got a personal token and tried to connect to Intercom via a web URL but without success. What is the best way to load Intercom data to Power BI?

Any feedback would be greatly appreciated! Many thanks.

Re: How to ensure correct encoding when importing parquet files into Power BI

tbakholdt — Fri, 23 Apr 2021 10:38:17 GMT

Hi

Thank you for your answers. However, this guide doesn't help with changing the encoding in order to keep the Danish letters, which is my problem.
I have no problem reading the actual data into PBI. However, I have trouble with encoding.

Re: Count Number of times a value in a column appears in another column in the same table. (DAX)

AlB — Fri, 23 Apr 2021 09:28:15 GMT

No. It should work fien with a calculated table, if you simply add a calculated column with the code above. Make sure to substitute Table3 with the actual name of your table. If it doesn'twork please share the pbix (with mock data if necessary)

Please accept the solution when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Seeing my database's structure with Power Query

ImStruggling — Fri, 23 Apr 2021 09:21:16 GMT

Hello,

I am an IT intern with some experience in databases but not a whole lot with Excel, and none with Power Query.
My small company requires me to make some pivot tables with relevant info from their SQL database, however in order to do that I would like first to have a clear idea of what the DB's structure is like.

I would like to display the database's general structure with power query, using an ODBC. However all I get when inputting the datasource is basically a list of all the tables in the DB.

Any ideas or suggestions? Thank you

Re: How to ensure correct encoding when importing parquet files into Power BI

v-kelly-msft — Fri, 23 Apr 2021 09:15:52 GMT

Hi ,

Sorry for the misunderstanding,check below blog for the steps below:

https://www.red-gate.com/simple-talk/blogs/power-bi-reading-parquet-from-a-data-lake/

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

Re: Power Query, M Code, Replace Value based on value in another column

tweidner — Fri, 23 Apr 2021 08:31:03 GMT

Thanks for the quick response... I would highly appreciate if you could explain the ,null, (x,y,z) => y

piece...

Is this just filling the formula requirement?

Rgds
T

Re: Count Number of times a value in a column appears in another column in the same table. (DAX)

rptmaxer — Fri, 23 Apr 2021 08:17:22 GMT

Thank you for lookng at this AIB, but intellisense won't allow me to choose the table and column to be the variable. Is it because it's a calcualted table?

Re: How to ensure correct encoding when importing parquet files into Power BI

tbakholdt — Fri, 23 Apr 2021 08:14:43 GMT

Hi

Yes, that works. But my question is more related to reading parquet files directly into PBI, and thereby avoiding a synapse ressource.

Re: How to ensure correct encoding when importing parquet files into Power BI

v-kelly-msft — Fri, 23 Apr 2021 08:11:14 GMT

Hi ,

Check the blog below:

https://techcommunity.microsoft.com/t5/azure-synapse-analytics/always-use-utf-8-collations-to-read-utf-8-text-in-serverless-sql/ba-p/1883633

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

Re: Unpivot and group columns

lkalawski — Fri, 23 Apr 2021 08:01:18 GMT

Sorry for the delay in answering, but only today I was able to test the solution.

, thank you very much for the code. It helps me to prepare data for visuals.

Re: JSON connector windows credentials over dataflow

shakirkhanani — Fri, 23 Apr 2021 07:56:25 GMT

Thank you for taking time out to look into the above issue.

However, the above issue was a false alaram, as it automatically fixed once I updated the gateway with the latest version.

Re: Power Query, M Code, Replace Value based on value in another column

CNENFRNL — Fri, 23 Apr 2021 07:43:42 GMT

Table.ReplaceValue(#"Changed Type", each if [Country] = "Oman" then "Offshore" else [Division], null, (x,y,z) => y, {"Division"})

Re: Extract the direct URL to view or edit a Sharepoint list item from PowerBI

v-yingjl — Fri, 23 Apr 2021 07:02:00 GMT

Hi ,

When you connect to SharePoint list in power bi, you can find the document table. Load it into power query and filter 'File' folumn, expand the 'LinkingUri' or 'LinkingUrl' to extract the URL of files in SharePoint list.

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Power Query, M Code, Replace Value based on value in another column

tweidner — Fri, 23 Apr 2021 06:46:14 GMT

Hello Everybody,

Apologies in case this was answered already in this forum, but I wasn't able to find it...

I've one column (Division) in a table where I would like to change the value if a value in another column (Country) fulfills a criteria.

Looks like:

Value in [Division] should change to "Offshore", if value in column [Country] = "Oman"

I thought I was close with the following formula, but nothing happens.

= Table.ReplaceValue(#"Changed Type", each [Country] = "Oman" , "Offshore" ,Replacer.ReplaceValue,{"Division"})

Any ideas?

Thank you & Rgds
Tobias

Re: The downloaded data is HTML, which isn't the expected type. The URL may be wrong ...

v-yingjl — Fri, 23 Apr 2021 06:37:00 GMT

Hi ,

Glad to hear the issue is solved. You can accept the suitable reply as solution, that way, other community members could easily find the answer when they get same issues.

Best Regards,
Community Support Team _ Yingjie Li

Re: Extract numbers from string and SUM them

asamr — Fri, 23 Apr 2021 06:35:48 GMT

Hi Rocco

The above did not work - Now Im gonna provide as much information as possible.

My Source is this:
Source = Table.FromColumns({Lines.FromBinary(File.Contents("C:\Users\asra\OneDrive - x a-s\PowerBI\xClub\revenue.json"), null, null)}),

Here is sample data:

Decimal numbers
Minus Quantity
Quantity

These should every possible case of data and should provide the most optimal results, if you are able to crack it.

Thank you for your time and help. This could never have been done without your contribution.
I am sorry for not providing everything in the beginning, I also learned all these different scenarios a long the way.

["13 01 2012", 43313, 41, 5, "{\"adults\":2,\"guest\":{\"first_name\":\"abc def\",\"last_name\":\"abc\",\"email\":\"x@stofanet.dk\",\"phone\":\"11111111\",\"customer_id\":\"451831\",\"club_id\":\"BjsT\"},\"addons\":[{\"code\":\"3-1-21143378\",\"price\":65,\"inventory_code\":\"80102\",\"quantity\":2},{\"code\":\"3-1-21143379\",\"price\":60,\"inventory_code\":\"80104\",\"quantity\":2}],\"price\":1400,\"comment\":[\"**** Anonymized \\/ Anonymiseret \\/ Anonymiserad ***\"]}"] ["31 05 2017", 43313, 63, 5, "{\"adults\":2,\"guest\":{\"first_name\":\"x x\",\"last_name\":\"x\",\"email\":\"x@stofanet.dk\",\"phone\":\"11223344\",\"customer_id\":\"451831\",\"club_id\":\"BjsT\"},\"price\":998,\"comment\":[\"**** Anonymized \\/ Anonymiseret \\/ Anonymiserad ***\"]}"] ["30 10 2011", 43315, 81, 5, "{\"adults\":2,\"guest\":{\"first_name\":\"x\",\"last_name\":\"x\",\"email\":\"x@storegade3.dk\",\"phone\":\"11223344\",\"customer_id\":\"661060\",\"club_id\":\"B-IC\"},\"addons\":[{\"code\":\"2-1-39\",\"price\":75,\"inventory_code\":\"9971\",\"quantity\":1},{\"code\":\"3-1-20499510\",\"price\":595,\"inventory_code\":\"c1070\",\"quantity\":1},{\"code\":\"3-1-20499522\",\"price\":595,\"inventory_code\":\"c1072\",\"quantity\":1},{\"code\":\"3-1-20499559\",\"price\":595,\"inventory_code\":\"c1072\",\"quantity\":1},{\"code\":\"3-1-20499682\",\"price\":595,\"inventory_code\":\"c1070\",\"quantity\":1},{\"code\":\"3-1-20499702\",\"price\":595,\"inventory_code\":\"c1070\",\"quantity\":1},{\"code\":\"3-1-20499513\",\"price\":595,\"inventory_code\":\"c1067\",\"quantity\":1},{\"code\":\"3-1-20499533\",\"price\":595,\"inventory_code\":\"c1067\",\"quantity\":1},{\"code\":\"3-1-20499560\",\"price\":595,\"inventory_code\":\"c1067\",\"quantity\":1},{\"code\":\"3-1-20499622\",... ["01 08 2012", 42587, 569, 5, "{\"adults\":2,\"children\":1,\"infants\":1,\"guest\":{\"first_name\":\"x\",\"last_name\":\"x\",\"email\":\"x@mail.dk\",\"phone\":\"11224455\",\"customer_id\":\"741525\",\"club_id\":\"sl_65182\"},\"price\":1487.5,\"comment\":[\"**** Anonymized \\/ Anonymiseret \\/ Anonymiserad ***\"]}"] ["13 11 2013", 42589, 593, 5, "{\"adults\":2,\"children\":2,\"guest\":{\"first_name\":\"x\",\"last_name\":\"x\",\"email\":\"x@x-cannon.dk\",\"phone\":\"55448877\",\"customer_id\":\"929022\",\"club_id\":\"dw_42589\"},\"addons\":[{\"code\":\"3-1-30136673\",\"price\":200,\"inventory_code\":\"5039\",\"quantity\":1},{\"code\":\"3-1-32709447\",\"price\":300,\"inventory_code\":\"5621\",\"quantity\":2}],\"price\":1350,\"comment\":[\"**** Anonymized \\/ Anonymiseret \\/ Anonymiserad ***\ ["12 07 2015", 42618, 833, 5, "{\"adults\":2,\"guest\":{\"first_name\":\"x\",\"last_name\":\"x\",\"email\":\"x@gmail.com\",\"phone\":\"77889944\",\"customer_id\":\"1002728\",\"club_id\":\"dw_42618\"},\"addons\":[{\"code\":\"3-1-38798558\",\"price\":0,\"inventory_code\":\"9867\",\"quantity\":2}],\"price\":2796}"] ["08 11 2015", 42621, 839, 5, "{\"adults\":2,\"guest\":{\"first_name\":\"S\\u00f8s\",\"last_name\":\"x\",\"email\":\"x@gmail.com\",\"phone\":\"11445566\",\"customer_id\":\"833061\",\"club_id\":\"dw_42621\"},\"addons\":[{\"code\":\"3-1-40950192\",\"price\":165,\"inventory_code\":\"E3034\",\"quantity\":2}],\"price\":1628,\"comment\":[\"**** Anonymized \\/ Anonymiseret \\/ Anonymiserad ***\"]}"] ["21 06 2013", null, 1025, 5, "{\"adults\":2,\"guest\":{\"first_name\":\"x-x\",\"last_name\":\"x\",\"email\":\"x@hotmail.com\",\"phone\":\"ukendt\",\"customer_id\":\"866978\",\"club_id\":\"dw_42675\"},\"addons\":{\"0\":{\"code\":\"3-1-28304381\",\"price\":175,\"inventory_code\":\"12034\",\"quantity\":2},\"4\":{\"code\":\"3-1-28615023\",\"price\":149,\"inventory_code\":\"c1140\",\"quantity\":1},\"5\":{\"code\":\"3-1-28615024\",\"price\":149,\"inventory_code\":\"c1140\",\"quantity\":1}},\"price\":2144,\"comment\":[\"**** Anonymized \\/ Anonymiseret \\/ Anonymiserad ***\"]}"] ["17 06 2013", 42730, 1197, 5, "{\"adults\":2,\"children\":2,\"guest\":{\"first_name\":\"x\",\"last_name\":\"x\",\"email\":\"x.x@telia.com\",\"phone\":\"77441122\",\"customer_id\":\"159296\",\"club_id\":\"sl_24507\"},\"addons\":{\"0\":{\"code\":\"3-1-13379496\",\"price\":195,\"inventory_code\":\"98890\",\"quantity\":2},\"1\":{\"code\":\"3-1-13379497\",\"price\":100,\"inventory_code\":\"98891\",\"quantity\":2},\"2\":{\"code\":\"3-1-13384836\",\"price\":190,\"inventory_code\":\"11101\",\"quantity\":-2},\"6\":{\"code\":\"2-1-34\",\"price\":219,\"inventory_code\":\"31098\",\"quantity\":2},\"7\":{\"code\":\"3-1-13379489\",\"price\":99,\"inventory_code\":\"32223\",\"quantity\":2}},\"price\":8206}"] ["09 11 2016", 42730, 1225, 5, "{\"adults\":2,\"guest\":{\"first_name\":\"x\",\"last_name\":\"x\",\"email\":\"x.x@telia.com\",\"phone\":\"88995566\",\"customer_id\":\"159296\",\"club_id\":\"sl_24507\"},\"addons\":{\"0\":{\"code\":\"2-1-34\",\"price\":219,\"inventory_code\":\"31098\",\"quantity\":2},\"3\":{\"code\":\"3-1-18899908\",\"price\":0,\"inventory_code\":\"31109\",\"quantity\":2},\"5\":{\"code\":\"2-1-34\",\"price\":219,\"inventory_code\":\"31098\",\"quantity\":2},\"7\":{\"code\":\"2-1-37\",\"price\":495,\"inventory_code\":\"31101\",\"quantity\":2},\"8\":{\"code\":\"3-1-18899910\",\"price\":495,\"inventory_code\":\"87262\",\"quantity\":-1}},\"price\":4467,\"comment\":[\"**** Anonymized \\/ Anonymiseret \\/ Anonymiserad ***\"]}"] ["18 09 2014", null, 1333, 5, "{\"adults\":3,\"children\":1,\"infants\":1,\"guest\":{\"first_name\":\"x\",\"last_name\":\"V\\u00f8lund\",\"email\":\"x@pc.dk\",\"phone\":\"11885566\",\"customer_id\":\"666001\",\"club_id\":\"sl_42914\"},\"price\":1946.5,\"comment\":[\"**** Anonymized \\/ Anonymiseret \\/ Anonymiserad ***\"]}"]

Re: Create table from columns

V-pazhen-msft — Fri, 23 Apr 2021 06:31:43 GMT

Follow the steps in power query:

1. Split the SharedWith column with semilcolon

2. Select the 3 columns and unpivot

3. Remove the attribute column

Paul Zheng _ Community Support Team
If this post helps, please Accept it as the solution to help the other members find it more quickly.

Re: Grabbing data with API, unable to convert datetime column to a date

v-jingzhang — Fri, 23 Apr 2021 06:08:49 GMT

You can add a custom column to contain only date values from the original datetime column. Just like below.

= Date.From([Old DateTime column])

This date column is able to be changed into Date type. You can remove the old datetime column and use this new date column later.

Regards,
Community Support Team _ Jing
If this post helps, please Accept it as the solution to help other members find it.

Re: Changing Data Source from Web link to SharePoint Folder.

mahoneypat — Fri, 23 Apr 2021 01:51:17 GMT

I think this is what you need. You may need to make sure there are no duplicate variables names and update those if so (I updated the ones I could see).

let Source = SharePoint.Files("https://healthineersapc.sharepoint.com/teams/iMS4CGlobal", [ApiVersion = 15]), #"Filtered Rows" = Table.SelectRows(Source, each ([Folder Path] = "https://healthineersapc.sharepoint.com/teams/iMS4CGlobal/IMS4C Global Data/On Time Completion of Changes(CM) KPI/Region/")), #"Filtered Hidden Files1" = Table.SelectRows(#"Filtered Rows", each [Attributes]?[Hidden]? <> true), #"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File (2)", each #"Transform File (2)"([Content])), #"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}), #"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File (2)"}), #"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File (2)", Table.ColumnNames(#"Transform File (2)"(#"Sample File (2)"))), #"Changed Type" = Table.TransformColumnTypes(#"Expanded Table Column1",{{"Source.Name", type text}, {"overdue", type text}, {"Cat", type text}, {"Change ID", type text}, {"Status", type text}, {"Assigned to", type text}, {"ChangeTitle", type text}, {"Change Description", type text}, {"Target Date", type date}, {"Created", type text}, {"CreatedDate", type date}, {"CreatedDAY", Int64.Type}, {"CreatedMONTH", Int64.Type}, {"CreatedMonthName", type text}, {"Quarter", type text}, {"CreatedYEAR", Int64.Type}, {"Created By", type text}, {"CAPA", type logical}, {"CAPA ID", type text}, {"Process Owner", type text}, {"assigned for Validation", type text}, {"ArchiveDate", type any}, {"Zone", type text}, {"Item Type", type text}, {"Path", type text}, {"OD", type any}, {"FirstReminder", type any}, {"CalculatedCounter", type any}, {"DateCreated", type any}, {"_Created", type datetime}, {"CM Status", type text}, {"CurrentDate", type datetime}, {"Overdue (days)", type any}, {"Overdue / Not overdue", type text}}), #"Split Column by Delimiter1" = Table.ExpandListColumn(Table.TransformColumns(#"Changed Type", {{"Zone", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Zone"), #"Filtered Rows3" = Table.SelectRows(#"Split Column by Delimiter1", each true), #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows3",{"overdue", "Assigned to", "ChangeTitle", "Change Description", "CreatedDAY", "CreatedMONTH", "CreatedMonthName", "Quarter", "CreatedYEAR", "Created By", "CAPA ID", "Process Owner", "assigned for Validation", "ArchiveDate", "Path", "OD", "FirstReminder", "CalculatedCounter", "DateCreated", "CurrentDate"}), #"Changed Type" = Table.TransformColumnTypes(#"Removed Columns",{{"CreatedDate", type date}}), #"Filtered Rows4" = Table.SelectRows(#"Changed Type", each ([Zone] <> " Global / iMS4C" and [Zone] <> "Global / iMS4C")), #"Trimmed Text" = Table.TransformColumns(#"Filtered Rows4",{{"Zone", Text.Trim, type text}}), #"Removed Columns1" = Table.RemoveColumns(#"Trimmed Text",{"_Created"}), #"Added Custom" = Table.AddColumn(#"Removed Columns1", "Refline 50%", each "0.5"), #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"Refline 50%", Percentage.Type}}), #"Removed Columns2" = Table.RemoveColumns(#"Changed Type1",{"Refline 50%"}), #"Changed Type2" = Table.TransformColumnTypes(#"Removed Columns2",{{"Target Date", type date}}), #"Filtered Rows2" = Table.SelectRows(#"Changed Type2", each [Target Date] <> null and [Target Date] <> "") in #"Filtered Rows2"

Pat

Re: If condition

v-jingzhang — Fri, 23 Apr 2021 01:42:11 GMT

You could add a pair of parentheses to wrap the third condition in this if statement. This should work.

Regards,
Community Support Team _ Jing
If this post helps, please Accept it as the solution to help other members find it.

Re: Count Number of times a value in a column appears in another column in the same table. (DAX)

AlB — Thu, 22 Apr 2021 23:54:15 GMT

Create a calculated column:

NewCol = VAR currentCol1_ = Table3[Column1] RETURN 0 + CALCULATE ( COUNT ( Table3[Column2] ), Table3[Column2] = currentCol1_, ALL ( Table3 ) )

Please accept the solution when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Count Number of times a value in a column appears in another column in the same table. (DAX)

rptmaxer — Thu, 22 Apr 2021 23:39:04 GMT

Hello, please could I have some help solving my issue?

I have two coulmns in a table called Projects.

Both columns contain the names of some projects.

I would like a third coulmn that counts how many times a project in coulmn A appears in column B.

Thank you for any help given.

N.B. The projects table is calculated a table from another table (don't know if this has any impact).

Calculate Per Unit Number when Sold Totals are within Data Field

triaa21 — Thu, 22 Apr 2021 22:45:09 GMT

Hello,

I am building a Power BI P&L using raw data from our accounting system. The data contains a list of accounts/entities with the appropriate values for each month. I would like to calculate per unit values for revenues, expenses, etc. However, the sold totals are in specific accounts within the data. I have tried to create custom fields that would filter out the sold data using if statements and calculate/filters. However, this only populates the sold date within the sold accounts. The attachedpictures are examples of how the data is set up, along with the results I'm getting trying to filter out the sold volume.

Re: How to filter a column based on a column from another table?

AlB — Thu, 22 Apr 2021 22:03:57 GMT

An inner join should do it and is quite probably the best performing option

Place the following M code in a blank query to see the steps.

let Source = Table.NestedJoin(Table1, {"Employee ID"}, Table2, {"Employee ID"}, "Table2", JoinKind.Inner), #"Removed Columns" = Table.RemoveColumns(Source,{"Table2"}) in #"Removed Columns"

Please accept the solution when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Re: How to get a custom values from a grouped table?

AlB — Thu, 22 Apr 2021 21:50:46 GMT

Place the following M code in a blank query to see the steps.

let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wcs4sqVRwVNJRilCK1UHiRqJyccs64eU6Y3IjcHJdUBWjcV1RFUO5sQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [CITY = _t, DATA = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"CITY", type text}, {"DATA", type text}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"CITY"}, {{"Res", each let aux_ = List.Distinct([DATA]) in if List.Contains(aux_, "X") then "X" else List.Select(aux_, each _ <>"X"){0} }}) in #"Grouped Rows"

Please accept the solution when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

How to get a custom values from a grouped table?

massotebernoull — Thu, 22 Apr 2021 20:47:25 GMT

I have a table with cities and data, like this:

CITY	DATA
City A	X
City A	Y
City A	X
City A	Y
City B	Y
City B	Y
City C	Y
City C	X
City C	X
City D	Y
City D	Y
City E	X
City E	X

I need to get a dimensional table from this table with single cities based mainly on X data. I need to prioritise if a city has X data. Then, if the city doesn't has X data, only Y, show her too. In the end, I need a table like this:

CITY	DATA
City A	X (has x and y, but prioritise x)
City B	Y (has only y)
City C	X (has x and y, but prioritise x)
City D	Y (has only y)
City E	X

I tried to group by city, then used Table.Contains in the data grouped, but I failed.

Can anyone help me, please?

Re: month to date to show nil for next month

SH-VE — Thu, 22 Apr 2021 20:16:30 GMT

thanks. Does ship date in this part of the DAX needs to be a query?

How to filter a column based on a column from another table?

rahme — Thu, 22 Apr 2021 19:27:47 GMT

I have Table 1 that contains a column of ALL of my employees:

Employee ID	Sales Value
1	25
2	50
3	75
4	100
5	100

I have Table 2 that contains a SUBSET of employees and their info:

Employee ID	First Name	Last Name
2	Jon	Doe
4	Jane	Doe

I want to filter my Table 1, so that it only contains rows that show up in Table 2. Table 1 after the filter should look like this:

Employee ID	Sales Value
2	50
4	100

How do I achieve this? Bonus: is there a way to achieve this without doing a Join? The tables are millions of rows long, so I don't know how join would impact performance.

Graph with multiple series

Dashmir — Thu, 22 Apr 2021 19:18:47 GMT

Context
I have two tables. I use table 2 to divide the value by the quantity.

Table 1 Column Names

Ref	Code	Food	?	Class	Category Header	Category	Value	Year	Source
Test	k00022	Chocolate	0	Sweet	Sales	Quarter 1	1,000.00	2016-17	XX

Table 2 Column Names
Code is unique.

Code	Quantity
k00022	100

Measure
In PowerBI I create a measure to divide Value / Quantity.

Graph
X axis = Year

I want to have the graph have two lines. One line for a particular food type e.g. chocolate and the cost per year.
I also want to have another line which is the averge of the value/quantity per class. So for blueberry which has a class of fruit, I want to have the average of fruit as a second line.

Re: Compare current period to the average of that period of the last X years.

ciervax — Thu, 22 Apr 2021 18:17:14 GMT

Thanks, Ying,

I've tried yesterday and today to solve the issue, this is what I have right now, focusing on RAIN column:

RAIN Comparison = AVERAGEX ( FILTER ( ALLEXCEPT ( 'WEATHER',WEATHER[ID1],WEATHER[Month] ), MONTH ( 'WEATHER'[DATE] ) >= 4 && MONTH ( 'WEATHER'[DATE] ) <= 7 && DAY ( 'WEATHER'[DATE] ) >= 1 && DAY ( 'WEATHER'[DATE] ) <= 16 && YEAR ( 'WEATHER'[DATE] ) <= CALCULATE ( YEAR ( MIN ( 'Calendar'[Date] ) ), ALLSELECTED ( 'Calendar'[Date] ) ) ), [RAIN] )

Result:

I've tried the following:

RAIN Comparison = AVERAGEX ( FILTER ( ALLEXCEPT ( 'WEATHER',WEATHER[ID1],WEATHER[Month] ), MONTH ( 'WEATHER'[DATE] ) >= 4 && MONTH ( 'WEATHER'[DATE] ) <= 7 && DAY ( 'WEATHER'[DATE] ) >= 1 && DAY ( 'WEATHER'[DATE] ) <= 16 && YEAR ( 'WEATHER'[DATE] ) <= CALCULATE ( YEAR ( MIN ( 'WEATHER'[DATE]) ), ALLSELECTED ( 'WEATHER'[DATE] ) ) ), [RAIN] )

But still no luck, I am not able to get in column rain, the rain of the current period, and RAIN comparison difference between the current period vs average of the last X years.

This is my target but with the correct numbers on the column "RAIN vsXy"

Thanks in advance, really appreciate your help.