Pivot Column in Query

[netanel]

Hey All!

I'm trying to make a column "Data Source" From rows to columns

But I get the following message?

Can anyone help?

[v-yanjiang-msft · Accepted solution]

Hi @netanel ,

The sample in case seems like not your fact table, right? The error means that PowerQuery is expecting a textual input but receives nothing. You need to make sure it receives a textual input. Try replacing empty fields by "null" or "empty". This can be done either manually within the source (transform the column or create a new column) or within PowerQuery.

About how to replace null values with custom values in PowerQuery:

https://www.edureka.co/community/40467/replace-null-values-custom-values-power-power-query-editor

Best Regards,
Community Support Team _ kalyj

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

[selimovd]

Hey @netanel ,

you can just select the "Data Source" Column and then chose "Pivot Column".

In the upcoming dialogue, choose "Sum" as aggregation type for the values:

Then the result will be as you desired:

Check here my example:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8vRT0lEyNFKK1YGxjZHYJkhsUyS2GRLbHMz2Dw0BcSyQOZZIHCMDZI4hMscImQO0PRYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Data Source" = _t, Amount = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Data Source", type text}, {"Amount", Int64.Type}}),
    #"Pivoted Column" = Table.Pivot(#"Changed Type", List.Distinct(#"Changed Type"[#"Data Source"]), "Data Source", "Amount", List.Sum)
in
    #"Pivoted Column"

If you need any help please let me know.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards

Denis

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic

[netanel]

That's exactly what I do and the result is
What I attached