[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『くえりでCSVデータの複数行を一行にできますでしょうか』（クエリ学習ちゅ）
いつも勉強させていただいています
下図のような作業明細CSVを一行にしたいのですが、可能でしょうか？
一つのSQについて、大体は10～20行程度あります。
作業SQ 時刻 作業 21010203077 12:00 a 21010203077 14:00 b 21010203077 15:13 b 21010203077 20:00 c 21010203077 22:00 D 21010204089 00:00 a 21010204089 12:00 b 21010204089 19:00 c 21010204089 20:00 c 21010204089 21:00 D
⇓
作業SQ 時刻1 時刻２ 時刻３ 時刻４ 時刻５ 21010203077 12:00 14:00 15:13 20:00 22:00 21010204089 00:00 12:00 19:00 20:00 21:00
⇓または
作業SQ 時刻1 時刻２ 時刻３ 時刻４ 時刻５ 21010203077 a12:00 b14:00 b15:13 c20:00 D22:00 21010204089 a00:00 b12:00 c19:00 c20:00 D21:00
よろしくお願いします
＜ 使用 Excel：Excel2010、使用 OS：Windows10 ＞
当方Excel2021ですが、以下で、後者はできました。
let
ToPivot=(TB as table) as table =>
let
Distinct=Table.Distinct(TB,{"時刻"}),
Pivot = Table.Pivot(Distinct, Distinct[時刻], "時刻", "作業SQ"),
Remove = Table.RemoveFirstN(Pivot,1),
DemoteHeader = Table.DemoteHeaders(Remove),
list1=Table.ColumnNames(DemoteHeader),
list2=List.Transform({1..List.Count(list1)}, each Text.Combine({"時刻",Text.From(_)})),
ret=Table.RenameColumns(DemoteHeader, List.Zip({list1,list2}))
in
ret,
ソース = Excel.CurrentWorkbook(){[Name="テーブル1"]}[Content],
Transform = Table.TransformColumns(ソース,{"時刻", each Time.ToText(Time.From(_), [Format="hh:mm", Culture="ja-JP"])}),
MaxRows =List.Max(Table.Group(Transform, {"作業SQ"}, {{"Count", each Table.RowCount(_), Int64.Type}})[Count]),
Group = Table.Group(Transform, {"作業SQ"}, {{"Group", each Table.CombineColumns(_,{"作業", "時刻"},Combiner.CombineTextByDelimiter(""),"時刻"), type table}}),
Pivot = Table.TransformColumns(Group,{"Group", each ToPivot(_)}),
ColumnNames=List.Transform({1..MaxRows}, each Text.Combine({"時刻",Text.From(_)})),
Expand = Table.ExpandTableColumn(Pivot, "Group", ColumnNames, ColumnNames)
in
Expand
前者は
Group = Table.Group(Transform,.....の行を↓で
Group = Table.Group(Transform, {"作業SQ"}, {{"Group", each Table.RemoveColumns(_,{"作業"}), type table}})
（まる2021） 2023/08/27(日) 20:35:03
Value=Record
Type=Type
（クエリ学習ちゅ） 2023/08/28(月) 06:09:53
元ソースは以下のような「CSVファイルをEXCELに読み込んでテーブル化（テーブル1）されている」 というのが前提ですが、そこは大丈夫ですか？
<テーブル1> 作業SQ 時刻 作業 21010203077 12:00 a 21010203077 14:00 b 21010203077 15:13 b 21010203077 20:00 c 21010203077 22:00 D 21010204089 00:00 a 21010204089 12:00 b 21010204089 19:00 c 21010204089 20:00 c 21010204089 21:00 D
Power Queryエディターの右ペイン「クエリの設定」「適用したステップ」で「ソース」を クリックした時、想定通りの表となっていますか？ 又、他の各ステップも確認してみてください。
Power Query限定ですか？VBAや関数でも良ければ、多数の回答が得られると思います。 （まる2021） 2023/08/28(月) 08:26:54
関数でやるなら、こんな感じ。2010でもいけると思います。
__A________ __B__ __C_ __D __E________ __F__ __G__ __H__ __I__ __J__
1 作業SQ 時刻 作業 作業SQ 時刻1 時刻2 時刻3 時刻4 時刻5
2 21010203077 12:00 a 21010203077 12:00 14:00 15:13 20:00 22:00
3 21010203077 14:00 b 21010204089 00:00 12:00 19:00 20:00 21:00
4 21010203077 15:13 b
5 21010203077 20:00 c
6 21010203077 22:00 D
7 21010204089 0:00 a
8 21010204089 12:00 b
9 21010204089 19:00 c
10 21010204089 20:00 c
11 21010204089 21:00 D
最大100行と仮定した場合、
[E1] =IFERROR(INDEX(A:A,AGGREGATE(15,6,ROW($1:$100)/(COUNTIF(INDIRECT("A1:A"&ROW($1:$100)),A$1:A$100)=1),ROW(A1))),"") ↓コピー
[F1] ="時刻"&COLUMN(A1) →コピー
[F2] =IFERROR(TEXT(INDEX($B$1:$B$100,AGGREGATE(15,6,ROW($A$1:$A$100)/($A$1:$A$100=$E2),COLUMN(A1))),"hh:mm"),"") →↓コピー
（まる2021） 2023/08/28(月) 08:52:06
>Expression.Error: 型 Record の値を型 Text に変換できません。 このエラーは、
Transform = Table.TransformColumns(ソース,{"時刻", each Time.ToText(Time.From(_), [Format="hh:mm", Culture="ja-JP"])}),
の行ででています。 Time.ToText の使い方がバージョンによって違うようです。
Transform = Table.TransformColumns(ソース,{"時刻", each Time.ToText(Time.From(_),"hh:mm")}),
と修正してみてください
（´・ω・`） 2023/08/28(月) 09:16:00
あら、そうなんですね。 （´・ω・`）さん、フォロー感謝です。
（まる2021） 2023/08/28(月) 09:29:30
ちょっとやってみました。別解ということで
let
ソース = Excel.CurrentWorkbook(){[Name="テーブル1"]}[Content],
変更された型 = Table.TransformColumnTypes(ソース,{{"作業SQ", Int64.Type}, {"時刻", type time}, {"作業", type text}}),
変更された型1 = Table.TransformColumnTypes(変更された型,{{"時刻", type text}}),
グループ化された行 = Table.Group(変更された型1, {"作業SQ"}, {{"カウント", each Table.AddIndexColumn(Table.FromList([時刻]), "インデックス", 1, 1), type table}}),
展開されたカウント = Table.ExpandTableColumn(グループ化された行, "カウント", {"Column1", "インデックス"}, {"時刻", "インデックス"}),
変更された型2 = Table.TransformColumnTypes(展開されたカウント,{{"インデックス", type text}, {"時刻", type text}}),
ピボットされた列 = Table.Pivot(変更された型2, List.Distinct(変更された型2[インデックス]), "インデックス", "時刻")
in
ピボットされた列
（´・ω・`） 2023/08/28(月) 12:59:42
なかなか、時間がとれません。
すみません。
テーブルかはしておらず、接続の状態で、クエリの詳細に追記しました。
接続のみでできるのを知ったので、読み込むと重くなるかと思い接続のみを使用しています。
今日中にはやってみます
（クエリ学習ちゅ） 2023/08/28(月) 15:14:35
（´・ω・`）様
ありがとうございます。
詳細エディタで続き追記してみたのですが、時刻がすべてNULLになってしまいます
また明日やってみますありがとうございました。
（クエリ学習ちゅ） 2023/08/28(月) 18:17:09
作業別にできないかと聞かれました。
作業SQ 時刻 作業 21010203077 12:00 a 21010203077 14:00 b 21010203077 15:13 b 21010203077 20:00 c 21010203077 22:00 F 21010204089 00:00 a 21010204089 12:00 b 21010204089 19:00 c 21010204089 20:00 c 21010204089 21:00 F
こんな感じ↓です。
作業SQ a b1 b2 b3 c1 c2 c3 d1 d2 d3 f
21010203077 12:00 14:00 15:13 20:00 22:00 21010204089 00:00 12:00 19:00 20:00 21:00
aとfは一回のみで間のｂ・ｃ・ｄについて時間を書きだしたい
出現回数は3回程度までが多いが、c・dは10ぐらいの時もあり、かなり多いのは除外
他にも作業はあるが、a・ｂ・ｃ・ｄ・ｆがあればとよいとのこと
（´・ω・`）様
すみません
「クエリの設定」「適用したステップ」をみてみると、展開されたカウントで時刻がnullになっております
何を確認したらよろしいでしょうか
すみません作業SQはテキストだったので、テキストにしてます。
他に使うデータ列があるのは削除しておいた方がいいのでしょうか？
（クエリ学習ちゅ） 2023/08/29(火) 11:11:54
分かりません なんででしょうね？ （´・ω・`） 2023/08/29(火) 11:42:26
let
ソース = Excel.CurrentWorkbook(){[Name="テーブル2"]}[Content],
変更された型 = Table.TransformColumnTypes(ソース,{{"作業SQ", type text}, {"時刻", type time}, {"作業", type text}}),
テキストに変更された時刻 = Table.TransformColumns(変更された型,{{"時刻",Text.From}}),
グループ化された行 = Table.Group(テキストに変更された時刻, {"作業SQ", "作業"}, {{"カウント", each Table.AddIndexColumn(Table.FromList([時刻]),"インデックス",1,1), type table}}),
展開されたカウント = Table.ExpandTableColumn(グループ化された行, "カウント", {"Column1", "インデックス"}, {"時刻", "インデックス"}),
追加されたカスタム = Table.AddColumn(展開されたカウント, "カスタム", each [作業]&Text.From([インデックス])),
削除された他の列 = Table.SelectColumns(追加されたカスタム,{"作業SQ", "時刻", "カスタム"}),
ピボットされた列 = Table.Pivot(削除された他の列, List.Distinct(削除された他の列[カスタム]), "カスタム", "時刻")
in
ピボットされた列
（´・ω・`） 2023/08/29(火) 12:40:09
すみません。
もう２～３お教えいただきたいのですけど、どうかお教えいただきたく思います。
〇並べ替えを自分ですればいいのかもしれませんが、
a1 b1 b2 c1 b3 f1 c2 c3 d1 d2 d3 のようになるのを作業単位の塊順はむずかしいでしょうか。
〇作業時刻の始まりで表にしていましたら、日付が変わることもあるため、経過時間ではどうかとなりましたので、時間でしたところ、「ピボットにされた列で」Timeに解析できませんとなって、25:10:20と表示されました。
最初にcsv読み込むとき数値にしたらよいのでしょうか
読み込んだ状態では時計のマークです。
〇クエリを参照で作成するときに、途中の「適用したステップ」のところを指定できますでしょうか
いろいろおうかがいすみません
（クエリ学習ちゅ） 2023/08/29(火) 15:40:58
列の並べ替えについては以下 mougのこちらのトピックを参考にしました パワークエリでの列自体の並べ替え https://www.moug.net/faq/viewtopic.php?t=82428
let
ソース = Excel.CurrentWorkbook(){[Name="テーブル4"]}[Content],
変更された型 = Table.TransformColumnTypes(ソース,{{"作業SQ", type text}, {"時刻", type duration}, {"作業", type text}}),
テキストに変更された時刻 = Table.TransformColumns(変更された型,{{"時刻",Text.From}}),
グループ化された行 = Table.Group(テキストに変更された時刻, {"作業SQ", "作業"}, {{"カウント", each Table.AddIndexColumn(Table.FromList([時刻]),"インデックス",1,1), type table}}),
展開されたカウント = Table.ExpandTableColumn(グループ化された行, "カウント", {"Column1", "インデックス"}, {"時刻", "インデックス"}),
追加されたカスタム = Table.AddColumn(展開されたカウント, "カスタム", each [作業]&Text.From([インデックス])),
削除された他の列 = Table.SelectColumns(追加されたカスタム,{"作業SQ", "時刻", "カスタム"}),
ピボットされた列 = Table.Pivot(削除された他の列, List.Distinct(削除された他の列[カスタム]), "カスタム", "時刻"),
並べ替えられた列 = Table.ReorderColumns(ピボットされた列, List.Sort(List.Skip(Table.ColumnNames(ピボットされた列),1),小文字で比較))
in
並べ替えられた列
小文字で比較というクエリ(関数)を以下のようにして作っておいてください。
let
ソース = (txt1 as text,txt2 as text)=>
let
ltxt1=Text.Lower(txt1),
ltxt2=Text.Lower(txt2),
ret = if ltxt1 < ltxt2 then -1 else if ltxt1<ltxt2 then 0 else 1
in
ret
in
ソース
その他の質問については、質問内容がよく把握できないので保留 もちょっと具体的にお願いします （´・ω・`） 2023/08/29(火) 17:00:47
厚かましいですが、お時間とれるときにでも、もう少し可能でしたら、お教えいただきたくよろしくお願いします。
列名が多いもので89まであり、下記のような順になるので D01の形にするか9までで終わりにする。
D1 D10 D11 D12 D13 D14 D15 D16 D17 D18 D19 D2 D20
D01 D02 D03 D04 D05 D06 D07 D08 D09 D10 D11 D12 D13
〇Timeに解析のは参考になるトピック見つけましたので何とかできそうな気がします。
〇クエリを参照で作成すると参照元を変えると参照先にも影響が出るので、参照の位置を固定できるたらと考えたのですが、無理と思いました
（クエリ学習ちゅ） 2023/08/30(水) 18:58:40
元表の「作業」列は「大文字/小文字」混在なのでしょうか？ 又、混在する場合、単にタイプミスではなく「大文字/小文字」は意味を持つものですか？ そのあたり、不明なのですべて大文字に統一して、列を自然ソートする例です。 それと、各作業は最大10としました。数を変えるなら「TOP10=...」の行の最後の「10」を変更してください。 （´・ω・`）さんの最後のコードで実験されているようなので、コードをお借りしています。
let
ソース = Excel.CurrentWorkbook(){[Name="テーブル2"]}[Content],
変更された型 = Table.TransformColumnTypes(ソース,{{"作業SQ", type text}, {"時刻", type duration}, {"作業", type text}}),
テキストに変更された時刻 = Table.TransformColumns(変更された型,{{"時刻",Text.From},{"作業",Text.Upper}}),
グループ化された行 = Table.Group(テキストに変更された時刻, {"作業SQ", "作業"}, {{"カウント", each Table.AddIndexColumn(Table.FromList([時刻]),"インデックス",1,1), type table}}),
TOP10=Table.TransformColumns(グループ化された行,{{"カウント",each Table.FirstN(_,10)}}),
展開されたカウント = Table.ExpandTableColumn(TOP10, "カウント", {"Column1", "インデックス"}, {"時刻", "インデックス"}),
追加されたカスタム = Table.AddColumn(展開されたカウント, "カスタム", each [作業]&Text.From([インデックス])),
削除された他の列 = Table.SelectColumns(追加されたカスタム,{"作業SQ", "時刻", "カスタム"}),
ピボットされた列 = Table.Pivot(削除された他の列, List.Distinct(削除された他の列[カスタム]), "カスタム", "時刻"),
並べ替えられた列 = Table.ReorderColumns(ピボットされた列, List.Sort(List.Skip(Table.ColumnNames(ピボットされた列),1),
{each if Text.Length(_)=2 then Text.Insert(_, 1, "0") else _}))
in
並べ替えられた列
（まる2021） 2023/08/30(水) 21:20:04
ついでに、 >関数の作り方勉強になりました。 は標準関数で、「Comparer.OrdinalIgnoreCase」があります。 https://learn.microsoft.com/ja-jp/powerquery-m/comparer-ordinalignorecase
なので、以下でもOK。もちろん独自関数（小文字）でもOK。
並べ替えられた列 = Table.ReorderColumns(ピボットされた列, List.Sort(List.Skip(Table.ColumnNames(ピボットされた列),1),Comparer.OrdinalIgnoreCase)) （まる2021） 2023/08/30(水) 21:55:10
>標準関数で、「Comparer.OrdinalIgnoreCase」 なるほどです
ついでに、ちょとだけ効率を求めてみました。 (1) 作業を10以下にするのをGroup化するときにやってしまう (2) 列の並べ替えは、Pivotの時にやってしまう
let
ソース = Csv.Document(File.Contents("D:\test.csv"),[Delimiter=",", Columns=3, Encoding=932, QuoteStyle=QuoteStyle.None]),
昇格されたヘッダー数 = Table.PromoteHeaders(ソース, [PromoteAllScalars=true]),
グループ化された行 = Table.Group(昇格されたヘッダー数, {"作業SQ", "作業"}, {{"カウント", each Table.AddIndexColumn(Table.FromList(List.FirstN([時刻],10)),"インデックス",1,1), type table}}),
展開されたカウント = Table.ExpandTableColumn(グループ化された行, "カウント", {"Column1" , "インデックス"}, {"時刻" , "インデックス"}),
追加されたカスタム = Table.AddColumn(展開されたカウント, "カスタム", each [作業]&Text.From([インデックス])),
削除された他の列 = Table.SelectColumns(追加されたカスタム,{"作業SQ", "時刻", "カスタム"}),
ピボットされた列 = Table.Pivot(削除された他の列, List.Sort(List.Distinct(削除された他の列[カスタム]),Comparer.OrdinalIgnoreCase), "カスタム", "時刻" )
in
ピボットされた列
（´・ω・`） 2023/08/31(木) 10:41:02
D1 D10 D2 D3・・・ 10だと上記のようになるので9にしました。
「適用したステップ」を順にみて、D01 D02 の形にするのも何とかなりました。
（´・ω・`）様
nullになったのが正解でデータが本当にNUllでその部分が見えていたことがわかりました。
本当にありがとうございました。
（クエリ学習ちゅ） 2023/08/31(木) 14:15:34
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.